Create (SQL)

A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation TABLEs, INDEXes, USERs, and DATABASEs. Some systems (such as PostgreSQL) allow CREATE and other DDL comands to occur inside of a transaction and thus be rolled back.

CREATE TABLE

You can use CREATE TABLE command to create a new table. With this command you specify a name, type for each field in the database to be created. The typical usage is as:

CREATE TABLE [table_name] 
  ([column_name type (size)] 
  ,[column_name type (size)] 
  , .....);

Column Defitions: A comma-separated list consisting of any of the following

Column definition: [column name] [data type] {NULL | NOT NULL} {column options}
Primary key definition: PRIMARY KEY ( [comma separated column list] )
CONTRAINTS: {CONSTRAINT}} [constraint definition]
RDBMS specific functionality

For example, the command to create a table named employee with a few sample columns would be:

CREATE TABLE employee 
  (empno         CHAR (4)   
  ,empfname      CHAR(50)  
  ,emplname      CHAR(75)  
  ,date_of_birth DATE      
  ,PRIMARY KEY (empno)
 );

For example, the command to create a table named teacher with a few sample columns would be:

CREATE TABLE teacher 
  (tno             CHAR (4)     
  ,tname           CHAR(45)     
  ,taddress        CHAR(65)     
  ,salary          NUMBER (5,2) 
  ,date_of_joining DATE         
  ,date_of_birth   DATE         
  ,dept_no         CHAR (4)     
  ,PRIMARY KEY (tno)
 );

Describing the Structure of the Table In SQL you can specify the structure of a table using the DESCRIBE command. The DESCRIBE command is used to show the attributes of the table with the constraint NULL/NOT NULL.

Suntax:     DESCRIBE <tablename>
            OR
            DESC <tablename>

For example to display the teacher table’s structure DESCRIBE teacher

The NOT NULL Constraints

This constraint ensures that the NULL values (empty values) are not permitted for a specified column. This constraint can be defined at the column level not at the table level.

CREATE TABLE teacher 
  (tno             CHAR (4)     NOT NULL
  ,tname           CHAR(45)     
  ,taddress        CHAR(65)     
  ,salary          NUMBER (5,2) 
  ,date_of_joining DATE         
  ,date_of_birth   DATE         
  ,dept_no         CHAR (4)     
  );

In the above example, TNO does not accept empty values.

The DEFAULT Constraints

A column may be given a default value through DEFAULT option. The DEFAULT values can be a SQL function such as SYSDATE as defined below in the teacher table.

CREATE TABLE teacher 
  (tno             CHAR (4) NOT NULL    
  ,tname           CHAR(45)     
  ,taddress        CHAR(65)     
  ,salary          NUMBER (5,2) 
  ,date_of_joining DATE  DEFAULT SYSDATE       
  ,date_of_birth   DATE         
  ,dept_no         CHAR (4)     
  );

The CHECK Constraints

The CHECK constraint explicitly defines a condition that each row must satisfy. There is no limit to the number of CHECK constraints that you can define on a column.

CREATE TABLE teacher 
  (tno             CHAR (4)     
  ,tname           CHAR(45)     
  ,taddress        CHAR(65)     
  ,salary          NUMBER (5,2) CHECK (SALARY BETWEEN 25000 AND 45000)
  ,date_of_joining DATE   DEFAULT SYSDATE      
  ,date_of_birth   DATE         
  ,dept_no         CHAR (4)     
  );

--203.115.103.150 10:58, 16 August 2005 (UTC) Amit Sehgal, G. D. Goenka World School