Background documentationForeign Key Dependencies Between Tables Locate this document in the navigation structure

 

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).

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

Foreign Key in Several Columns: You can define a foreign key for several primary key columns.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

SQL Reference Manual, CONSTRAINT Definition (referential_constraint_definition)

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Foreign Key

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

Creating a Foreign Key When Defining 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. Define the hotel table as illustrated below:

    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 simple foreign key hotel_zip_in_city for the zip column

CITY Table

ZIP

NAME

STATE

HOTEL Table

HNO

NAME

ZIP

...

The name of the foreign key 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)

Creating Foreign Keys for an Existing Table Retroactively

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 basic foreign key was added to customer number column cno in table reservation. This column is a primary key column in the customer table.

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 multiple columns, that is, 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), 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 Information

More examples for Data Definition