CREATE TABLE Instruction
The CREATE TABLE instruction defines a table. The definition includes:
• Table name
• The names and attributes of its columns.
• The attributes of the table such as its primary and foreign keys.
The syntax is as follows:
CREATE TABLE table-name (col1-name col1-type [NOT NULL]
...
name-coln type-coln [NOT NULL])
For DB2, the syntax is as follows:
CREATE TABLE table-name (col1-name col1-type [NOT NULL]
...
name-coln type-coln [NOT NULL])
[in Tablespace <Name>]
For Oracle, the syntax is as follows:
CREATE TABLE table-name (col1-name col1-type [NOT NULL]
...
name-coln type-coln [NOT NULL])
[Tablespace <Name>]
• table name : “SQL” value for the table, or else defaults to the name of the table; unrecognized characters are replaced by "_"
• col-name: Value of the SQL Name attribute for the column, or by default the name of the column; unrecognized characters are replaced by “_”
• col-type
• Tablespace: DB2 and Oracle: Name of the target tablespace for the tables
The PRIMARY KEY clause is generated within the CREATE TABLE command (see
PRIMARY KEY clause).
Managing NOT NULL
Clauses NULL, NOT NULL and NOT NULL WITH DEFAULT are generated automatically on the columns of primary keys and on columns derived from obligatory attributes at time of synchronization.
These values can be initialized as "Null", "Not Null" or "Not Null with Default" as a function of configuration defined in the database Properties dialog box for Not Null Columns, in the Synchronization subtab of the Options tab.
The values proposed can then be modified on each column.
PRIMARY KEY clause
Defining a primary key
One or more of the columns in a table can be used to uniquely identify each row in the table. Values in these columns must be specified. They act as the primary key for the table.
A table must have only one primary key or none.
Each column name must identify a column in the table, and the column cannot be identified more than once.
Processing and generating SQL commands
After declaring the names of the columns in the table, if the PRIMARY KEY option is enabled, the name(s) of the columns in the primary key are declared as follows:
PRIMARY KEY (list of columns in the primary key)
The PRIMARY KEY clause is generated within the CREATE TABLE command.
|
Example 1
|
Example 2
|
|
The primary key "PK" has only one column, "PK-col".
CREATE TABLE table-name
(PK-col CHAR(9) NOT NULL,
info1 CHAR(7),
info2 CHAR(7),
PRIMARY KEY (PK-col)
|
The primary key "PK1" has columns "PK11" and "PK12".
CREATE TABLE table-name
PK11 CHAR(9) NOT NULL,
PK12 CHAR(9) NOT NULL,
info1 CHAR(7),
info2 CHAR(7),
PRIMARY KEY (PK11, PK12)
|
For Oracle, the complete PRIMARY KEY clause is as follows:
CONSTRAINT PK_<key name> (list of columns in the primary key)
FOREIGN KEY clause
Database integrity can be ensured either by FOREIGN KEY clauses or by generated triggers, depending on the target DBMS. (In Oracle, it is ensured either with triggers, or with FOREIGN KEY clauses, depending on the database configuration.)
One or more columns in a table can refer to a primary key in this table or in another table. These columns form the foreign key. These columns do not need to have a value in each row.
The table containing the referenced primary key is a parent table. The table containing the foreign key is a dependent table.
Each column name must identify a single column in the table, and the same column cannot be identified more than once. If the same list of column names is specified in more than one FOREIGN KEY clause, these clauses cannot identify the same table.
The table name specified in the FOREIGN KEY clause must identify a parent table. A foreign key in a dependent table must have the same number of columns as the primary key for the parent table.
The number of foreign keys is unlimited.
Processing and generating SQL commands
After declaring the primary keys (PRIMARY KEY), the column name(s) for the foreign key(s) are declared for a table using FOREIGN KEY:
FOREIGN KEY (list of columns in the foreign key) REFERENCES <Parent table name> [ON DELETE <Action>] [ON UPDATE <Action>]
or:
ALTER TABLE tablename [ADD] FOREIGN KEY (list of columns for the foreign key) REFERENCES <Parent table name > [ON DELETE <Action>] [ON UPDATE <Action>]
For Oracle, the syntax is as follows:
CONSTRAINT FK_<name of the foreign key> (list if columns for the foreign key) REFERENCES <Parent table name > [ON DELETE <Action>] [ON UPDATE <Action>]
or:
ALTER TABLE...
Examples
The table "table1-name" has two foreign keys. These keys have no components.
CREATE TABLE table1-name
pk1 CHAR(9) NOT NULL,
pk2-rel12 CHAR(7) NOT NULL,
pk3-rel13 CHAR(7) NOT NULL,
info1 CHAR(7),
info2 CHAR(7),
PRIMARY KEY (pk1))
ALTER TABLE table1-name ADD FOREIGN KEY(cp2-rel12) REFERENCES table2-name
ALTER TABLE table2-name ADD FOREIGN KEY(cp3-rel13) REFERENCES table3-name
The table “table1-name” has a foreign key “fk2” which has two components, “fk21” and “fk22”. The foreign key "fk2" has no reference (it is therefore a component of the primary key of another table).
CREATE TABLE table1-name
pk1 CHAR(9) NOT NULL,
fk21 CHAR(7) NOT NULL,
fk22 CHAR(7) NOT NULL,
info1 CHAR(7),
PRIMARY KEY (pk1))
ALTER TABLE table1-name ADD FOREIGN KEY (fk21, fk22)
REFERENCES table2-name
The table “table1-name” has a foreign key, “fk2”. The foreign key “fk2” is equivalent to the primary key “pk2” which has two components, “pk21” and “pk22”. The columns identified by “pk21” and “pk22” are “NOT NULL”.
CREATE TABLE table1-name
pk1 CHAR(9) NOT NULL,
pk21 CHAR(7) NOT NULL,
pk22 CHAR(7) NOT NULL,
info1 CHAR(7),
info2 (CHAR7),
PRIMARY KEY (pk1))
ALTER TABLE table1-name ADD FOREIGN KEY (pk21, pk22)
REFERENCES table2-name
UNIQUE clause
A UNIQUE clause is generated for each unique index in the table, unless this index corresponds to the primary key.
Processing and generating SQL commands
For each unique index, the following clause is generated:
UNIQUE (col1,...,coln)
(col1,...n,coln) represent the columns used by the index.