Constraint Types
Types of constraints are:
• Unique
• Referential
• Primary key
Unique Constraints
You can define unique constraints at both the column and the table level. Columns that you specify as unique or that you use as part of a table-level unique constraint cannot be nullable. Column-level unique constraints ensure that no two rows in the table can have the same value for that column. At the table level, you can specify several columns, all of which are taken together to determine uniqueness.
For example, if you specify the department number and department location columns to be unique at the table level, no two departments in the same location can have the same name:
CREATE TABLE depts ( dname CHAR(10) NOT NULL,
dlocation CHAR(10) NOT NULL)
CONSTRAINT unique_dept UNIQUE (dname, dlocation));
The columns are declared NOT NULL, as required by the unique constraint. You can specify a maximum of 32 columns in a table-level unique constraint; however, a table can have any number of unique constraints.
In contrast, specifying the department name and department location columns to be unique at the column level is more restrictive—in this case, no two departments can have the same name, regardless of the location, and no two locations can have the same name either.
CREATE TABLE dept (dname CHAR(10) UNIQUE NOT NULL,
dlocation CHAR(10) UNIQUE NOT NULL);
In Director or VDBA define column-level unique constraints by enabling the Unique check box for the column. In VDBA, define table-level unique constraints using the Table Level Unique Constraint dialog.
Referential Constraints
Referential constraints are used to validate an entry against the contents of a column in another table (or another column in the same table), allowing you to maintain the referential integrity of your tables. In SQL, use the REFERENCES option of the CREATE TABLE and ALTER TABLE statements.
For information on referential action options, see the SQL Reference Guide.
When defining a referential constraint, you must consider the following points:
• The table that you intend to reference must exist, with the appropriate primary key or unique constraint defined.
• Referencing columns from the table in which the constraints are being defined are compared to columns that make up the primary key or a table-level unique constraint in the referenced, or parent, table.
• The data types of the columns must be comparable, and the referencing columns must correspond in number and position to those in the referenced table.
• You must have references permission for the referenced columns.
Example: Define a Referential Constraint
The following example of a referential constraint assumes that the employee table exists with a primary key constraint defined involving a name and an employee number column.
This example verifies the contents of the name and empno columns in the manager table against the primary key columns in the employee table, to ensure that anyone entered into the table of managers is on file as an employee.
CREATE TABLE manager (name CHAR(10),
empno CHAR(5),
...
FOREIGN KEY (name, empno) REFERENCES emp);
Primary Key Constraint
Primary key constraints can be used to denote one or more columns, which other tables reference in referential constraints.
Note: Primary key constraints can be used as an alternative and slightly more restrictive form of unique constraint, but need not be used at all.
To define a primary key, you choose which columns are to be part of the key and assign to each a particular position in the key. Columns that are part of the primary key cannot be nullable, and the primary key is implicitly unique. A table can have only one primary key, which can consist of a maximum of 32 columns.
To create a Primary Key constraint, use the SQL CREATE TABLE or ALTER TABLE statements.
Example: Define a Primary Key Constraint
For example, using SQL, in the partnumbers table, define the partno column as the primary key. The inventory table has a comparable column named ipartno. A referential constraint can be defined on the inventory table based on the partnumbers table.
CREATE TABLE partnumbers
(partno INTO NOT NULL PRIMARY KEY,
desc VARCHAR(20),
type CHAR(1) NOT NULL);
CREATE TABLE inventory
(ipartno INT NOT NULL,
start_date DATE,
type CHAR(1) NOT NULL,
end_date DATE,
qty INT NOT NULL);
ALTER TABLE inventory ADD CONSTRAINT fk_ipart FOREIGN KEY (ipartno) REFERENCES partnumbers;
In this case, the part numbers in the inventory table are checked against those in the partnumbers table. When defining this referential constraint, it is not necessary to specify the column to be referenced in the partnumbers table because it was defined as the primary key.