skip to main content
Reference : Supported SQL Statements and Extensions : Create Table : Constraint Definition for Local Tables
  

Try DataDirect Drivers Now
Constraint Definition for Local Tables

Purpose

Defines the syntax to define a contraint for a local table.

Syntax

[CONSTRAINT [constraint_name]
{unique_constraint |
primary_key_constraint |
foreign_key_constraint}]
where:
constraint_name
specifies a name for the constraint.
unique_constraint
specifies a constraint on a single column in the table. See Unique Clause for syntax.
Values in the constrained column cannot be repeated, except in the case of null values. For example:
ColA

1

2

NULL

4

5

NULL
A single table can have multiple columns with unique constraints.
primary_key_constraint
specifies a constraint on one or more columns in the table. See Primary Key Clause for syntax.
Values in a single column primary key column must be unique. Values across multiple constrained columns cannot be repeated, but values within a column can be repeated. Null values are not allowed. For example:
Col A Col B

2 1

3 1

4 2

5 2

6 2
Only one primary key constraint is allowed in the table.
foreign_key_constraint
defines a link between related tables. See Foreign Key Clause for syntax.
A column defined as a foreign key in one table references a primary key in the related table. Only values that are valid in the primary key are valid in the foreign key. The following example is valid because the foreign key values of the dept id column in the EMP table match those of the id column in the referenced table DEPT:
Referenced Table
Main Table
DEPT
EMP
(Foreign Key)
id
name
id
name
dept id
1
Dev
1
Mark
1
2
Finance
1
Jim
3
3
Sales
1
Mike
2
The following example, however, is not valid. The value 4 in the dept id column does not match any value in the referenced id column of the DEPT table.
Referenced Table
Main Table
DEPT
EMP
(Foreign Key)
id
name
id
name
dept id
1
Dev
1
Mark
1
2
Finance
1
Jim
3
3
Sales
1
Mike
4

Unique Clause

UNIQUE (column_name [,column_name...])
where:
column_name
specifies the column to which the constraint is applied. Multiple columns names must be separated by commas.

Primary Key Clause

PRIMARY KEY (column_name [,column_name...])
where:
column_name
specifies the primary key column to which the constraint is applied. Multiple column names must be separated by commas.

Foreign Key Clause

FOREIGN KEY (fcolumn_name [,fcolumn_name...])
REFERENCES ref_table (pcolumn_name [,pcolumn_name...])
[ON {DELETE | UPDATE}
{CASCADE | SET DEFAULT | SET NULL}]
fcolumn_name
specifies the foreign key column to which the constraint is applied. Multiple column names must be separated by commas.
ref_table
specifies the table to which a foreign key refers.
pcolumn_name
specifies the primary key column or columns referenced in the referenced table. Multiple column names must be separated by commas.
ON DELETE
is a clause that defines the operation performed when a row in the table referenced by a foreign key constraint is deleted. One of the following operators must be specified in the On Delete clause:
where:
*CASCADE specifies that all rows in the foreign key table that reference the deleted row in the primary key table are also deleted.
*SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the deleted row in the primary key table.
*SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the deleted row in the primary key table.
ON UPDATE
is a clause that defines the operation performed when the primary key of a row in the table referenced by a foreign key constraint is updated. One of the following operators must be specified in the On Update clause:
*CASCADE specifies that the value of the foreign key column for all rows in the foreign key table that reference the row in the primary key table that had the primary key updated are updated with the new primary key value.
*SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.
*SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.

Notes

*Remote tables are not supported.
*You must specify at least one constraint.
*Both the On Delete and On Update clauses can be used in a single foreign key definition.

Example

Assuming the current schema is PUBLIC, the emp table is created with the name, empId, and deptId columns. The table contains a foreign key constraint on the deptId column that references the id column in the dept table. In addition, it sets the value of any rows in the deptId column to NULL that point to a deleted row in the referenced dept table.
CREATE TABLE emp (name VARCHAR(30), empId INTEGER, deptId INTEGER, FOREIGN KEY(deptId) REFERENCES dept(id)) ON DELETE SET NULL)