A rule is a simple statement of cause and effect, carried out by the RI system defined in the database.
For example, a delete rule defines what happens to records containing a foreign key when a record containing a primary key is deleted: “When the record containing ‘Bhargava Building’ is deleted, all rows in Table A that reference that record are deleted.”
An update rule defines what happens to a record containing a foreign key when a user attempts to update the record or add a new record: “When a user attempts to insert a new record to Table B, reject the attempt if the building name does not exist in Table C.”
A primary key is the column or columns upon which a rule depends. Only one primary key is permitted in any table, and the primary key must not allow duplicate values. For an update rule, the primary key is the column or columns against which updated or inserted columns are compared to determine if the updated or inserted record should be allowed.
In Example A, the column containing “Bhargava Building” is the primary key.
In Example B, the column in Table C that contains the building name is the primary key.
A foreign key is the column or columns that are compared against a primary key to determine how to proceed.
In Example A above, the column in Table A that may contain the value “Bhargava Building” is the foreign key.
In Example B above, the column in Table B that contains the building name is the foreign key.
A cascade rule is a rule in which the database permits the desired operation to occur, then enforces RI by changing other tables or rows to synchronize with the first operation. For example, if a
delete cascade rule is defined, deleting a record in the primary key table causes the database to find and delete all rows throughout the database that have foreign key values the same as the primary key value of the deleted row.
A restrict rule is a rule in which the database decides whether or not to permit the desired operation based on existing values in the database. For example, if an update restrict rule is defined, an attempt to add a row to a table containing a foreign key causes the database engine to compare the value in the foreign key field to the values in the primary key. If there is no primary key row with the same value, the new row is not permitted to be added to the foreign key table.
In the example shown above, the column named student_ID in Table A (
A.student_ID) is an IDENTITY data type that does not allow two rows to the have the same value. Every student has a unique ID number. We will define
student_ID as the primary key of Table A.
We can then define the column named stud_ID in Table B (
B.stud_ID) as a foreign key that references
A.student_ID. Note that the data type of
stud_ID must be a type that can be compared with IDENTITY, such as INTEGER. The data types of primary and foreign keys must be compatible. You can have as many foreign keys as you need in order to enforce your desired referential integrity scheme. Multiple foreign keys can reference the same primary key.
The table with the primary key can be referred to as the parent table, while the table with the foreign key is called the child table. Once the keys are defined, we have a range of behaviors to choose from, as shown in Table 20. You can define as many rules as fit your needs, but you can only have one of each type. For example, if you define a delete restrict rule, you cannot define a delete cascade rule on the same keys, because the two behaviors are mutually exclusive.
Continuing with the example, setting an update restrict rule ensures that the value of B.stud_ID in any new or updated row must first exist in
A.student_ID. It follows, then, that you must have rows in Table A before you can add any rows in Table B. Stated another way, you must create at least one parent row before you can create a child row.
PSQL does not allow circular delete cascade on tables that reference each other. For example, consider the following scenario in which you have tables d1 and d2:
The following alter statement is not allowed because tables d1 and d2 already have a delete cascade relationship: