Defining Integrity Constraints on a Table

Go Up to Creating Tables (Data Definition Guide)

InterBase allows you to optionally apply certain constraints to a column, called integrity constraints, which are the rules that govern column-to-table and table-to-table relationships, and validate data entries. They span all transactions that access the database and are automatically maintained by the system. Integrity constraints can be applied to an entire table or to an individual column.

PRIMARY KEY and UNIQUE Constraints

The PRIMARY KEY and UNIQUE integrity constraints ensure that the values entered into a column or set of columns are unique in each row. If you try to insert a duplicate value in a PRIMARY KEY or UNIQUE column, InterBase returns an error. When you define a UNIQUE or PRIMARY KEY column, determine whether the data stored in the column is inherently unique. For example, no two social security numbers or driver’s license numbers are ever the same. If no single column has this property, then define the primary key as a composite of two or more columns which, when taken together, are unique.

The EMPLOYEE table

`EMP_NO`	`LAST_NAME`	`FIRST_NAME`	`JOB_TITLE`	`PHONE_EXT`
10335	Smith	John	Engineer	4968
21347	Carter	Catherine	Product Manager	4967
13314	Jones	Sarah	Senior Writer	4800

In the EMPLOYEE table, EMP_NO is the primary key that uniquely identifies each employee. EMP_NO is the primary key because no two values in the column are alike. If the EMP_NO column did not exist, then no other column is a candidate for primary key due to the high probability for duplication of values. LAST_NAME, FIRST_NAME, and JOB_TITLE fail because more than one employee can have the same first name, last name, and job title. In a large database, a combination of LAST_NAME and FIRST_NAME could still result in duplicate values. A primary key that combines LAST_NAME and PHONE_EXT might work, but there could be two people with identical last names at the same extension. In this table, the EMP_NO column is actually the only acceptable candidate for the primary key because it guarantees a unique number for each employee in the table.

A table can have only one primary key. If you define a PRIMARY KEY constraint at the table level, you cannot do it again at the column level. The reverse is also true; if you define a PRIMARY KEY constraint at the column level, you cannot define a primary key at the table level. You must define the NOT NULL attribute for a PRIMARY KEY column in order to preserve the uniqueness of the data values in that column.

Like primary keys, a unique key ensures that no two rows have the same value for a specified column or ordered set of columns. You must define the NOT NULL attribute for a UNIQUE column. A unique key is different from a primary key in that the UNIQUE constraint specifies alternate keys that you can use to uniquely identify a row. You can have more than one unique key defined for a table, but the same set of columns cannot make up more than one PRIMARY KEY or UNIQUE constraint for a table. Like a primary key, a unique key can be referenced by a foreign key in another table.

Using the FOREIGN KEY to Enforce Referential Integrity

A foreign key is a column or set of columns in one table that correspond in exact order to a column or set of columns defined as a primary key in another table. For example, in the PROJECT table, TEAM_LEADER is a foreign key referencing the primary key, EMP_NO in the EMPLOYEE table.

The PROJECT table

`PROJ_ID`	`TEAM_LEADER`	`PROJ_NAME`	`PROJ_DESC`	`PRODUCT`
`DGPII`	44	Automap	blob data	hardware
`VBASE`	47	Video database	blob data	software
`HWRII`	24	Translator upgrade	blob data	software

The EMPLOYEE table

`EMP_NO`	`LAST_NAME`	`FIRST_NAME`	`DEPT_NO`	`JOB_CODE`	`PHONE_EXT`	`SALARY`
24	Smith	John	100	Eng	4968	64000
48	Carter	Catherine	900	Sales	4967	72500
36	Smith	Jane	600	Admin	4800	37500

The primary reason for defining foreign keys is to ensure that data integrity is maintained when more than one table uses the same data: rows in the referencing table must always have corresponding rows in the referenced table.

InterBase enforces referential integrity in the following ways:

The unique or primary key columns must already be defined before you can create the foreign key that references them.
Referential integrity checks are available in the form of the ON UPDATE and ON DELETE options to the REFERENCES statement. When you create a foreign key by defining a column or table REFERENCES constraint, you can specify what should happen to the foreign key when the referenced primary key changes. The options are:

Action specified	Effect on foreign key
`RESTRICT` `NO ACTION`	[Default] The foreign key does not change (can cause the primary key update or delete to fail due to referential integrity checks)
`CASCADE`	The corresponding foreign key is updated or deleted as appropriate to the new value of the primary key
`SET DEFAULT`	Every column of the corresponding foreign key is set to its default value; fails if the default value of the foreign key is not found in the primary key
`SET NULL`	Every column of the corresponding foreign key is set to `NULL`

If you do not use the ON UPDATE and ON DELETE options when defining foreign keys, you must make sure that when information changes in one place, it changes in all referencing columns as well. Typically, you write triggers to do this. For example, to change a value in the EMP_NO column of the EMPLOYEE table (the primary key), that value must also be updated in the TEAM_LEADER column of the PROJECT table (the foreign key).
If you delete a row from a table that is a primary key, you must first delete all foreign keys that reference that row. If you use the ON DELETE CASCADE option when defining the foreign keys, InterBase does this for you.

When you specify SET DEFAULT as the action, the default value used is the one in effect when the referential integrity constraint was defined. When the default for a foreign key column is changed after the referential integrity constraint is set up, the change does not have an effect on the default value used in the referential integrity constraint.

You cannot add a value to a column defined as a foreign key unless that value exists in the referenced primary key. For example, to enter a value in the TEAM_LEADER column of the PROJECT table, that value must first exist in the EMP_NO column of the EMPLOYEE table.

The following example specifies that when a value is deleted from a primary key, the corresponding values in the foreign key are set to NULL. When the primary key is updated, the changes are cascaded so that the corresponding foreign key values match the new primary key values.

CREATE TABLE PROJECT {
. . .
TEAM LEADER INTEGER REFERENCES EMPLOYEE (EMP_NO)
ON DELETE SET NULL
ON UPDATE CASCADE
. . .};

Referencing Tables Owned by Others

If you want to create a foreign key that references a table owned by someone else, that owner must first use the GRANT command to grant you REFERENCES privileges on that table. Alternately, the owner can grant REFERENCES privileges to a role and then grant that role to you. See Planning Security and the Language Reference for more information on granting privileges to users and roles. See the Language Reference for more on creating and dropping roles.

Circular References

When two tables reference each other’s foreign keys and primary keys, a circular reference exists between the two tables. In the following illustration, the foreign key in the EMPLOYEE table, DEPT_NO, references the primary key, DEPT_NO, in the DEPARTMENT table. Therefore, the primary key, DEPT_NO must be defined in the DEPARTMENT table before it can be referenced by a foreign key in the EMPLOYEE table. In the same manner, EMP_NO, which is the primary key of the EMPLOYEE table, must be created before the DEPARTMENT table can define EMP_NO as its foreign key.

The problem with circular referencing occurs when you try to insert a new row into either table. Inserting a new row into the EMPLOYEE table causes a new value to be inserted into the DEPT_NO (foreign key) column, but you cannot insert a value into the foreign key column unless that value already exists in the DEPT_NO (primary key) column of the DEPARTMENT table. It is also true that you cannot add a new row to the DEPARTMENT table unless the values placed in the EMP_NO (foreign key) column already exist in the EMP_NO (primary key) column of the EMPLOYEE table. Therefore, you are in a deadlock situation because you cannot add a new row to either table!

InterBase gets around the problem of circular referencing by allowing you to insert a NULL value into a foreign key column before the corresponding primary key value exists. The following example illustrates the sequence for inserting a new row into each table:

Insert a new row into the EMPLOYEE table by placing “1” in the EMP_NO primary key column, and a NULL in the DEPT_NO foreign key column.
Insert a new row into the DEPARTMENT table, placing “2” in the DEPT_NO primary key column, and “1” in the foreign key column.
Use ALTER TABLE to modify the EMPLOYEE table. Change the DEPT_NO column from NULL to “2.”

How to Declare Constraints

When declaring a table-level or a column-level constraint, you can optionally name the constraint using the CONSTRAINT clause. If you omit the CONSTRAINT clause, InterBase generates a unique system constraint name which is stored in the RDB$RELATION_CONSTRAINTS system table.

Tip:
To ensure that the constraint names are visible in RDB$RELATION_CONSTRAINTS, commit your transaction before trying to view the constraint in the RDB$RELATION_CONSTRAINTS system table.

The syntax for a column-level constraint is:

<col_constraint> = [CONSTRAINT constraint] <constraint_def>
 [<col_constraint> ...]
<constraint_def> =
UNIQUE | PRIMARY KEY
 | CHECK (<search_condition>)

 | REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
</pre>

The syntax for a table-level constraint is:

<source lang=sql>
<tconstraint> = [CONSTRAINT constraint] <tconstraint_def>
 [<tconstraint> ...]
<tconstraint_def> = {PRIMARY KEY | UNIQUE} (col [, col …])
 | FOREIGN KEY (col [, col …])
 REFERENCES other_table [(other_col [, other_col …])]
 [ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
 | CHECK (<search_condition>)

Tip:
Although naming a constraint is optional, assigning a descriptive name with the CONSTRAINT clause can make the constraint easier to find for changing or dropping, and easier to find when its name appears in a constraint violation error message.

The following statement illustrates how to create a simple, column-level PRIMARY KEY constraint:

CREATE TABLE COUNTRY
(COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
CURRENCY VARCHAR(10) NOT NULL);

The next example illustrates how to create a UNIQUE constraint at both the column level and the table level:

CREATE TABLE STOCK
 (MODEL SMALLINT NOT NULL UNIQUE,
 MODELNAME CHAR(10) NOT NULL,
 ITEMID INTEGER NOT NULL,
CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));

Advance To:

Defining a CHECK Constraint