Foreign Key Dependencies Between Tables 

You can define a dependency between two tables that affects the tables when changes are made to rows. This relationship is called a referential constraint (or a foreign key dependency).

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

SQL Reference Manual, Referential CONSTRAINT Definition (referential_constraint_definition)

Creating a Foreign Key

You can specify a foreign key (referential constraint) when you define the table or add it to an existing table at a later stage.

Simple Foreign Key

You can define foreign key dependencies that refer to only one primary key column in a table.

You can use the CREATE TABLE statement to define foreign keys when you create a table.

...

       1.      Delete the hotel table.
DROP TABLE hotel.hotel

       2.      Create the hotel table as follows:
CREATE TABLE hotel.hotel
(hno     FIXED(4) PRIMARY KEY CONSTRAINT hno_cons CHECK hno > 0,
 name    CHAR(50) NOT NULL,
 zip     CHAR(5)  CONSTRAINT zip_cons CHECK
                  SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND
                  SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND
                  SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND
                  SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND
                  SUBSTR(zip,5,1) BETWEEN '0' AND '9',
 address CHAR(40) NOT NULL,
 FOREIGN KEY hotel_zip_in_city (zip) REFERENCES hotel.city ON DELETE RESTRICT)

You can link the city and hotel tables to one another on the basis of the zip code, since the zip code column in the city table is the primary key column. On the basis of the FOREIGN KEY syntax rule, the hotel table is assigned the foreign key (referential constraint) hotel_zip_in_city for the zip column

CITY Table

HOTEL Table

HNO

NAME

ZIP

....

The name of the foreign key (referential constraint) hotel_zip_in_city was specified after the keywords FOREIGN KEY. If you do not specify the name, the database system itself assigns the following name: city_hotel, that is, the names of the tables involved are linked by an underscore.

By specifying the appropriate keyword before the keyword DELETE, you can define what you want to happen to dependent values when rows are deleted.

For example, if you want to delete rows from the city table only if no hotel entries are available for these cities, you can select from among the following options:

●     ON DELETE RESTRICT: the system outputs a warning; the rows are not deleted from the city table.

●     ON DELETE CASCADE: the relevant rows are deleted from the city and hotel tables.

●     ON DELETE SET NULL: obsolete entries in the hotel table are assigned the NULL value.

●     ON DELETE SET DEFAULT: obsolete entries in the hotel table are assigned the default value.

The foreign key hotel_zip_in_city prevents you from inserting or changing a row in the hotel table for which no city is available.

 

See also:

SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

 

You can use the ALTER TABLE statement to add foreign keys to existing tables.

 

ALTER TABLE hotel.reservation ADD FOREIGN KEY reservation_cno_in_customer (cno) REFERENCES hotel.customer ON DELETE CASCADE

A foreign key was added to the customer number column in the reservation table. This column is a primary key column in the customer table.

 

Foreign Key in Several Columns

You can define a foreign key for several primary key columns.

 

ALTER TABLE hotel.reservation ADD FOREIGN KEY reservation_info_in_room (hno,type) REFERENCES hotel.room ON DELETE CASCADE

A foreign key was added to the hno and type columns in the reservation table. These columns are primary key columns in the room table.

 

See also:

SQL Reference Manual, ALTER TABLE Statement (alter_table_statement)

SQL Reference Manual, ADD Definition (add_definition)

Evaluating System Tables, FOREIGNKEYS, FOREIGNKEYCOLUMNS

 

Deleting a Foreign Key

You can use the ALTER TABLE statement to delete foreign keys.

 

ALTER TABLE hotel.reservation DROP FOREIGN KEY reservation_info_in_room

 

See also:

SQL Reference Manual, DROP Definition (drop_definition)

More examples for Data Definition