Column-Level Constraints and Table-Level Constraints

Constraints for individual columns can be specified as part of the column specification (column-level constraints) or for groups of columns as part of the table definition (table-level constraints).

The constraint has the following syntax:

[CONSTRAINT constraint_name] constraint

• constraint

  Is either a column-level constraint (column_constraint) or table-level constraint (table_constraint).

  column_constraint is one or more of the following:

  UNIQUE [WITH constraint_with_clause]

  PRIMARY KEY [WITH constraint_with_clause]

  REFERENCES [schema.]table_name[(column_name)]
      [WITH constraint_with_clause]

  table_constraint is one or more of the following:

  UNIQUE (column_name {, column_name}) [WITH constraint_with_clause]

  PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]

  FOREIGN KEY (column_name {, column_name})

  REFERENCES [schema.]table_name [(column_name {, column_name})]
      [WITH constraint_with_clause]

• constraint_name

  Defines a name for the constraint. If the constraint name is omitted, the DBMS Server assigns one. The constraint name is used when dropping the constraint (using the ALTER TABLE statement).

  Note: We recommend defining a name when creating a constraint; otherwise system catalogs must be queried to determine the system-defined name.

Examples

Here is an example of column-level constraints:

create table mytable(name char(10) not null,
        id integer references idtable(id),
        age integer check (age > 0));

Note: Multiple column constraints are separated by a space.

Here is an example of table-level constraints:

create table yourtable(firstname char(20) not null,
            lastname char(20) not null,
            unique(firstname, lastname));

Using Create Table...As Select

The CREATE TABLE...AS SELECT syntax creates a table from another table or tables. The new table is populated with the set of rows resulting from execution of the specified SELECT statement.

Note: The CREATE TABLE...AS SELECT syntax is an Ingres extension and not part of the ANSI/ISO Entry SQL-92 standard.

By default, the storage structure of the table is heap with compression. To override the default, issue the SET result_structure statement prior to issuing the CREATE TABLE...AS SELECT statement or specify the WITH STRUCTURE option.

By default, the columns of the new table have the same names as the corresponding columns of the base table from which you are selecting data. Different names can be specified for the new columns.

The data types of the new columns are the same as the data types of the source columns. The nullability of the new columns is determined as follows:

• If a source table column is nullable, the column in the new table is nullable.
• If a source table column is not nullable, the column in the new table is defined as not null.

If the source column has a default value defined, the column in the new table retains the default definition. However, if the default value in the source column is defined using an expression, the default value for the result column is unknown and its nullability depends on the source columns used in the expression. If all the source columns in the expression are not nullable, the result column is not nullable. If any of the source columns are nullable, the result column is nullable.

A system_maintained logical key column cannot be created using the CREATE TABLE...AS SELECT syntax. When creating a table using CREATE TABLE...AS SELECT, any logical key columns in the source table that are reproduced in the new table are assigned the format of NOT SYSTEM_MAINTAINED.