An interrelation between two tables can be defined which will influence the modification of rows.
table customer
Such a relation is called a referential integrity constraint and determined when the table 'reservation' is defined. An interrelation between the table 'reservation' and 'customer' is defined by assigning a so-called foreign key to 'reservation'. This foreign key corresponds to the key of 'customer'.
CREATE TABLE reservation
(rno FIXED (4) KEY,
cno FIXED (4),
hno FIXED (4),
roomtype CHAR (6),
arrival DATE,
departure DATE,
FOREIGN KEY (cno)
REFERENCES customer
ON DELETE CASCADE)
The name of the referential integrity constraint can be specified by the user after the keywords FOREIGN KEY, or it will be given by the database itself. In the latter case, the names of the tables concerned are concatenated - separated by an underscore - (up to a maximum length of 18 characters).
The relation defined in the example above gets the name 'customer_reservation'.
A keyword to be specified after DELETE can be used to determine what is to be done with depending values when rows are being deleted.
When rows of the table 'customer' are being deleted, it must be ensured that there are no reservations any more for this table. If there are some, the user can choose among different possibilities:
He specifies
ON DELETE RESTRICT
in the statement. Then he gets a warning and can act accordingly.
He requires that the corresponding reservation rows are implicitly deleted as well.
ON DELETE CASCADE
But he can also achieve that the customer number, which has become meaningless, is set to the NULL value or a default value in the affected rows of the reservation table:
ON DELETE SET NULL
ON DELETE SET DEFAULT
The relation defined above is also supervised the other way round. Inserting a new reservation or updating an existing reservation for which no customer exists is prevented irrespective of the DELETE rule.
A special ALTER TABLE statement can be used to delete a referential integrity constraint.
The relation between the tables 'reservation' and 'customer' is no longer desired and must therefore be removed again:
ALTER TABLE reservation DROP FOREIGN KEY customer_reservation