What is a foreign key?
Essentially a foreign key means that values in one table must also appear in another table. Generally the referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table. To expand this concept a foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding record or records in the child table with automatically be deleted. This is referred to as a cascade delete. A foreign key with a cascade delete can be defined in one of two ways either with a CREATE TABLE statement or with an ALTER TABLE statement.
Examples using Oracle PL/SQL:
Using a CREATE TABLE statement
| CREATE TABLE table_name |
| (column1 datatype null/not null, |
| column2 datatype null/not null, |
| ... |
| CONSTRAINT fk_column |
| FOREIGN KEY (column1, column2, ... column_n) |
| REFERENCES parent_table (column1, column2, ... column_n) |
| ON DELETE CASCADE |
| ); |
To illustrate take for example the following:
| CREATE TABLE parent |
| ( parent_id numeric(10) not null, |
| parent_name varchar2(50) not null, |
| parent_info varchar2(50), |
| CONSTRAINT parent_pk PRIMARY KEY (parent_id) |
| ); |
| |
| CREATE TABLE child |
| ( child_id numeric(10) not null, |
| parent_id numeric(10) not null, |
| CONSTRAINT fk_parent |
| FOREIGN KEY (parent_id) |
| REFERENCES parent(parent_id) |
| ON DELETE CASCADE |
| ); |
| |
In this example, we've created a primary key on the parent table called parent_pk. It consists of only one field - the parent_id field. Then we've created a foreign key called fk_parent on the child table that references the parent table based on the parent_id field.
Because of the cascade delete, when a record in the parent table is deleted, all records in the child table will also be deleted that have the same parent_id value.
Using an ALTER TABLE statement
| ALTER TABLE table_name |
| add CONSTRAINT constraint_name |
| FOREIGN KEY (column1, column2, ... column_n) |
| REFERENCES parent_table (column1, column2, ... column_n) |
| ON DELETE CASCADE; |
To illustrate take for example the following:
| ALTER TABLE child |
| add CONSTRAINT fk_parent |
| FOREIGN KEY (parent_id) |
| REFERENCES parent(parent_id) |
| ON DELETE CASCADE; |
In this example, we've created a foreign key (with a cascade delete) called fk_parent that references the parentr table based on the parent_id field.