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 employees with a few sample columns would be:

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

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

CREATE TABLE teachers 
  (tno             CHAR (4)     not null
  ,tname           CHAR(45)     null
  ,taddress        CHAR(65)     not null
  ,salary          NUMBER (5,2) null
  ,date_of_joining DATE         not null
  ,date_of_birth   DATE         null
  ,dept_no         CHAR (4)     null
  ,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. Syntax: 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.

Example: CREATE TABLE TEACHER (TNO. CHAR (4) NOT NULL, TNAME CHAR (25), TADDRESS CHAR (45), SALARY NUMBER(7,2), DEPT_NO. CHAR(4), DOJ DATE); 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.

Example: CREATE TABLE TEACHER (TNO. CHAR (4) NOT NULL, TNAME CHAR (25), TADDRESS CHAR (45), SALARY NUMBER(7,2), DEPT_NO. CHAR(4), DOJ DATE DEFAULT SYSDATE);

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.

Example: CREATE TABLE TEACHER (TNO. CHAR (4) NOT NULL, TNAME CHAR (25), TADDRESS CHAR (45), SALARY NUMBER(7,2) CHECK (SALARY BETWEEN 5000 AND 10000), DEPT_NO. CHAR(4), DOJ DATE DEFAULT SYSDATE);

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