CREATE TABLE

Name

CREATE TABLE  --  Creates a new table.

Synopsis

CREATE [ TEMPORARY | TEMP ] TABLE table_name {
    (column_name type
    [column_constraint[...]]
     |table_constraint)[,...])
    [INHERITD (inherited_table [,...])]

where column_constraint can be:
    [CONSTRAINT constraint_name]
    { NOT NULL | NULL ] [ UNIQUE | PRIMARY KEY
    | DEFAULT value
    | CHECK (condition)
    | REFERENCES table
      [ (column) ]
    [ MATCH FULL | MATCH PARTIAL ]
    [ ON DELETE action ]
    [ ON UPDATE action ]
   [ DEFERRABLE | NOT DEFERRABLE ]
   [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

and table constraint can be:

   [ CONSTRAINT  constraint_name ]
   { UNIQUE (  column_name [, ... ] ) |
     PRIMARY KEY (  column_name [, ... ] ) |
     CHECK (  condition ) |
     FOREIGN KEY (  column_name [, ... ] )
     REFERENCES  table
   [ ( column [, ... ] ) ]
   [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE  action ]
   [ ON UPDATE  action ]
   [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
   }
  

Parameters

TEMPORARY or TEMP

Pass this parameter to specify that the table being created will be destroyed after the user's session has ended. Any table-related constructions (such as indexes and constraints) will also be destroyed with the table at the end of the session.

NoteWarning
 

If temporary table is given the same name as an existing permanent table, only the temporary table will be seen. This can be useful, but it can also cause problems.

table_name

This parameter takes the name of the table you are creating.

column_name

Use this parameter to provide the name of a column you want created within the new table (most tables will have multiple columns).

type

Use this parameter to specify the column's type. This can be a standard type, or (alternatively) you can specify that this column is an array.

constraint_name

This parameter is optional. Use it to provide the name of a column or table constraint. If you do not specify this, the database will supply one.

value

This parameter allows you to provide a default value to set column data if a value is not provided by INSERT . See the DEFAULT clause for more information.

condition

Use this condition to define tests that all created and updated rows must pass in order for the modification to be completed. This condition must return a true or false (boolean) result.

NoteTable Constraint vs. Column Constraint
 

A table constraint can include multiple columns, whereas a column constraint only creates a constraint for a single column.

column

Use this parameter to provide the name of a column you are referencing in a foreign key constraint. The column must reside within an existing table. If no column name is given, the database will use the referenced table's primary key is used.

table

Use this parameter to specify the name of a table you wish to be referenced by a foreign key constraint.

inherited_table

This clause is optional. Use it to provide table names you wish the new table to inherit columns from. If there are any column names inherited from the table that match column names you've already specified as columns for the new table, CREATE TABLE will display an error and end.

action

Use this parameter to specify a keyword that represents what action should be taken if a foreign key constraint is violated during modification of the table.

Results

CREATE

This message is displayed if the table is successfully created.

ERROR

This error message is displayed if creation of the table fails. Normally this will be followed by an explanation of the error.

Description

Use the CREATE TABLE command to add a new table into the database you are connected to. After creation, the new table will be completely empty and its ownership will be set to the user who issued the CREATE TABLE command.

You must supply a type for each column that that new table will be comprised of (except for inherited columns; the type will be assumed). This can be a standard type (integer, char, etc.), complex type (a set), or an array type. You may stipulate that a column not accept null values by using the NOT NULL constraint. To

NoteWarning
 

Tables cannot have the same name as existing data types; nor can they have the same names as system catalog tables.

A table can have a maximum of about 1600 columns; however, bceause of tuple-length restraints, this number is lower in practice.

DEFAULT Clause

DEFAULT value
   

Use the DEFAULT clause to define a default value you wish assigned to this column when an INSERT is received that does not provide a value. The value can be any variable-free expression that matches the column data type. If no default value is provided, any column that does not have a provided value will be set to NULL.

Column CONSTRAINT Clause

[ CONSTRAINT constraint_name ] { [
     NULL | NOT NULL ] | UNIQUE | PRIMARY KEY
     | CHECK condition
     | REFERENCES reftable
     (refcolumn)
     [ MATCH matchtype ]
     [ ON DELETE action ]
     [ ON UPDATE action ]
     [ [ NOT ] DEFERRABLE ]
     [ INITIALLY checktime ] }
    [, ...]
   

Parameters

constraint_name

Use this parameter to specify the name of the new constraint clause.

NULL

Use this keyword to allow the column to contain NULL values. This option is set by default.

NOT NULL

Use this keyword to forbid the use of a NULL value for this column. You can accomplish this by using the CHECK ( column NOT NULL) column constraint.

UNIQUE

Use this keyword to force all rows within this column to have unique values (unique within the table). This is enforced by the creation of a unique index on the column.

PRIMARY KEY

Use this keyword to set this column as a primary key for the table. Other tables rely on primary keys to act as the identifying column for each row. A primary key is effectively set as UNIQUE and NOT NULL.

CHECK

Use this keyword to have values checked against the specified condition; if the condition proves false for the value, the new row will be rejected,

REFERENCES

Use this keyword to have column values checked against the values of a referenced column.

condition

An arbitrary boolean-valued constraint condition.

Description

The use of constraint clauses within a table (column or table constraints) is optional. Use them to setup tests that check the values of rows being inserted or updates. Each row must meet the rules defined by whatever constraint checks you have specified for the column, or the INSERT/UPDATE that attempted to add them will fail.

Column constraints are the column-level integrity constraints that are included with the definition of a column. The column constraints available are: PRIMARY KEY, REFERENCES, UNIQUE, CHECK, and NOT NULL.

Table CONSTRAINT Clause

[ CONSTRAINT name ] { PRIMARY KEY |  UNIQUE } ( column [, ...] )
[ CONSTRAINT name ] CHECK ( condition )
[ CONSTRAINT name ] FOREIGN KEY ( column [, ...] ) 
     REFERENCES reftable (refcolumn [, ...] )
         [ MATCH matchtype ] 
         [ ON DELETE action ] 
         [ ON UPDATE action ]
         [ [ NOT ] DEFERRABLE ] 
         [ INITIALLY checktime ]
   

Parameters

CONSTRAINT name

Use this parameter to supply the name of the constraint to be created.

column [, ...]

Use this parameter to specify that a column is a unique index. If the column you specify is set as PRIMARY KEY, it will now be set as NOT NULL. Alternatively, you can supply multiple column names.

UNIQUE

Use this keyword to have the specified column's value checked for dupiclity. Any new rows that do not contain a unique value for this column are disregarded.

PRIMARY KEY

Use this keyword to set the specified column as a primary key for the table.

CHECK ( constraint )

Use this keyword to check a value against the supplied boolean constraint before a new row is inserted; if the check fails (i.e., the constraint evaluated to false), the row is not added.

FOREIGN KEY

Use this keyword to identify a column that will act as a foreign key.

Examples

The following example creates a table with columns that use the NOT NULL constraint. The invoice_num column is the primary key.

CREATE TABLE shipped_orders  
     (
     invoice_num INTEGER CONSTRAINT firstkey PRIMARY KEY,
     subtotal MONEY NOT NULL,
     cust_id  INTEGER NOT NULL,
     ship_date DATE,
      );