Creating a Table (CREATE TABLE)

Creating a Table (CREATE TABLE)
Having created the database structure, we may now create the table structures for the base
relations to be located in the database. This is achieved using the CREATE TABLE statement,
which has the following basic syntax:
CREATE TABLE TableName
{(columName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption] [CHECK (searchCondition)] [, . . . ]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns)] [, . . . ]}
{[FOREIGN KEY (listOfForeignKeyColumns)
REFERENCES ParentTableName [(listOfCandidateKeyColumns)]
[MATCH {PARTIAL | FULL}
[ON UPDATE referentialAction]
[ON DELETE referentialAction]] [, . . . ]}
{[CHECK (searchCondition)] [, . . . ]})
As we discussed in the previous section, this version of the CREATE TABLE statement
incorporates facilities for defining referential integrity and other constraints. There is
significant variation in the support provided by different dialects for this version of the
statement. However, when it is supported, the facilities should be used.
The CREATE TABLE statement creates a table called TableName consisting of one or
more columns of the specified dataType. The set of permissible data types is described in
Section 6.1.2. The optional DEFAULT clause can be specified to provide a default value
for a particular column. SQL uses this default value whenever an INSERT statement fails
to specify a value for the column. Among other values, the defaultOption includes literals.
The NOT NULL, UNIQUE, and CHECK clauses were discussed in the previous section.
The remaining clauses are known as table constraints and can optionally be preceded
with the clause:
CONSTRAINT ConstraintName
which allows the constraint to be dropped by name using the ALTER TABLE statement
(see below).
The PRIMARY KEY clause specifies the column or columns that form the primary key
for the table. If this clause is available, it should be specified for every table created. By
default, NOT NULL is assumed for each column that comprises the primary key. Only
one PRIMARY KEY clause is allowed per table. SQL rejects any INSERT or UPDATE
operation that attempts to create a duplicate row within the PRIMARY KEY column(s).
In this way, SQL guarantees the uniqueness of the primary key.
The FOREIGN KEY clause specifies a foreign key in the (child) table and the
relationship it has to another (parent) table. This clause implements referential integrity
constraints. The clause specifies the following:
n A listOfForeignKeyColumns, the column or columns from the table being created that form
the foreign key.
n A REFERENCES subclause, giving the parent table; that is, the table holding the
matching candidate key. If the listOfCandidateKeyColumns is omitted, the foreign key is
assumed to match the primary key of the parent table. In this case, the parent table must
have a PRIMARY KEY clause in its CREATE TABLE statement.
n An optional update rule (ON UPDATE) for the relationship that specifies the action
to be taken when a candidate key is updated in the parent table that matches a foreign
key in the child table. The referentialAction can be CASCADE, SET NULL, SET
DEFAULT, or NO ACTION. If the ON UPDATE clause is omitted, the default NO
ACTION is assumed (see Section 6.2).
n An optional delete rule (ON DELETE) for the relationship that specifies the action to
be taken when a row is deleted from the parent table that has a candidate key that
matches a foreign key in the child table. The referentialAction is the same as for the
ON UPDATE rule.
n By default, the referential constraint is satisfied if any component of the foreign key is
null or there is a matching row in the parent table. The MATCH option provides additional
constraints relating to nulls within the foreign key. If MATCH FULL is specified,
the foreign key components must all be null or must all have values. If MATCH
PARTIAL is specified, the foreign key components must all be null, or there must be
at least one row in the parent table that could satisfy the constraint if the other nulls
were correctly substituted. Some authors argue that referential integrity should imply
MATCH FULL.
There can be as many FOREIGN KEY clauses as required. The CHECK and
CONSTRAINT clauses allow additional constraints to be defined. If used as a column
constraint, the CHECK clause can reference only the column being defined. Constraints
are in effect checked after every SQL statement has been executed, although this check can
be deferred until the end of the enclosing transaction (see Section 6.5). Example 6.1
demonstrates the potential of this version of the CREATE TABLE statement.