Slide 9.6: SQL CREATE TABLE statement

Slide 9.5: Oracle built-in datatypes
Slide 9.7: SQL CREATE TABLE statement (cont.)
Home Print version

SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

The figure shows the simplified syntax of CREATE TABLE where

CREATE TABLE table-name (
  column-name-1 type [NOT NULL | UNIQUE] [, column-name-2 ... ]
  [  AS SELECT ... FROM ... [ WHERE ... ] ]
  [, UNIQUE ( list-of-column-names ) [, UNIQUE ... ] ]
  [, PRIMARY KEY ( list-of-column-names ) ]
  [, FOREIGN KEY ( list-of-column-names )
      REFERENCES  table-name-2 [ ( list-of-column-names ) ]
  [, FOREIGN KEY ... ] ]
  [, CHECK ( condition ) [, CHECK ... ] ] )

The square brackets [ ] mean the enclosed items are optional and the bar | means OR.

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g., VARCHAR, INTEGER, DATE, etc.).

The NOT NULL constraint enforces a column to NOT accept NULL values, and the UNIQUE constraint ensures that all values in a column are different.

The AS SELECT phrase is to populate the new table by using the existing table content.

Key (PRIMARY KEY) means an attribute or attribute combination whose values uniquely identify the tuples of any relation.

Foreign key (FOREIGN KEY) defined for a schema R, however, describes an attribute or attribute combination which is a key in another schema S (REFERENCES); the meaning of a foreign key relationship between R and S via attribute X is that the X part of R is a subset of the X part of S.

The CHECK constraint is used to limit the value range that can be placed in a column. The condition like (salary>0 AND salary<300000) must be met for the check constraint to succeed. If a constraint fails, a warning occurs and the insert or update for any offending row is skipped.

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support