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.
SQL Reference Manual, CONSTRAINT Definition (referential_constraint_definition)
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.
Delete the hotel table.
DROP TABLE hotel.hotel
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
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.
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