CREATING TABLE, TABLE LEVEL CONSTRAINT

Creating A Table


To create your own table in oracle, “Create Table” command is used. Create Table command has the following syntax.



Create Table TabName
(col1 datatype [constraint constname] [constspec],
col2 datatype [constraint constname] [constspec],
.
.
.
coln datatype [constraint constname] [constspec]
);

Here Col1, Col2, … , Coln are the column names to be included in the table. Datatype is any valid datatype of SQL. Constraint is keyword. Constname is the name for the constraint specifying on that column. Constspec is the constraint to be given on that column. Specifying constraint name is optional. When you want to give a name to the constraint, then only you have to use the key word constraint. If we don’t specify a name to the constraint, then oracle will automatically give a name to that constraint. You can get the constraint names from data dictionary view user_constraints. Most of oracle’s generated constraint names are of the form SYS_C######. For example, SYS_C000145. Even specifying a constraint on a column is also optional.
These are the basic elements of this command.
The words create table
The name of the table
An opening paranthesis
Column definitions
A closing paranthesis
A SQL terminator

The individual column definitions are separated by commas. There is no comma after the last column definition. The table and column names must start with a letter of the alphabet, but may include letters, numbers, and underscores. Names may be 1 to 30 characters in length, must be unique within the table, and can not be an oracle reserved word.
Example :

Create Table STUDENT
(Sno Number(5) constraint SNO_PK Primary Key,
Sname Varchar2(15) NOT NULL,
Address Varchar2(20) NOT NULL,
Course Varchar2(1) NOT NULL,
Fee Number(7,2)
);

Example :

Create table Marks
(Sno Number(5) constraint SNO_FK references Student(Sno),
M1 Number(3) constraint M1_CHK Check(M1<=100),
M2 Number(3) constraint M2_CHK Check(M2<=100),
M3 Number(3) constraint Mn_CHK Check(M3<=100),
Total Number(5),
Aveg Number(7,2)
);


Table Level Constraints

Constraints can be classified into column level constraints and table level constraints. If constraint was specified immediately after column definition then that constraint is called as column level constraint and if a constraint was specified at the end of the create table statement, then that constraint is called as table level constraint. Table level constraints are generally used when constraint has to be given on more than one column. An example of table level constraint is as follows. In this example, primary key constraint is given on two columns invno and itemno.

Create table Invoice
(Invno number(5) ,
Cname varchar2(20) not null,
Itemno number(5),
Qty number(3) not null,
Uprice number(7,2) not null,
Constraint invitem_pk primary key(invno,itemno)
);

Creating a Table From Another Table

To create a new table from an existing table, the following syntax is used.

Syntax : create table tablename as select statement on the source table;
Example : the following example creates emp1 by selecting the structure and rows of emp table.

Create table Emp1 as select * from emp;

Displaying Structure of The Table

To display the structure of a table i.e. the column names and their data types, describe command is used.

Syntax : DESC TableName