Constraints
When you create or alter a table, define constraints for the table. Constraints are used to check for appropriate data values whenever data is entered or updated in the table.
Constraints are checked at the end of every statement that modifies the table. If the constraint is violated, the DBMS returns an error and aborts the statement. If the statement is in a multi-statement transaction, the transaction is not aborted.
Define constraints using the CREATE TABLE or ALTER TABLE statements. In Director, use the New Table page.
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 can 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 name 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));
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);
Unique keys can be nullable. There can be multiple rows with NULL. For multi-column keys (table-level unique constraint), uniqueness is enforced only on keys without NULL in any of the key columns.
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 Language 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.
• Referential constraints are enforced only on non-null entries.
• 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.