lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Foreign Key Constraint


A foreign key constraint enforces a relationship between a parent table and a child table.

Also known as a referential integrity constraint, a foreign key ensures that a value in a column (or columns) on the child table exists as a primary key value on the parent table.

For example.
CREATE TABLE continent (
  cont_id    NUMBER        PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL
);

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);
Now we have a foreign key on the COUNTRY table (the child) which references the primary key value on the CONTINENT table (the parent).

This constraint will ensure that the relationship between the two is always correct. In other words, a row on the COUNTRY table will always have a valid reference to a row on the CONTINENT table.
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 999999, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
-- SQL Error: ORA-02291: integrity constraint (LISH.SYS_C0014665) violated - parent key not found

UPDATE country
SET cont_id = 999999;
-- SQL Error: ORA-02291: integrity constraint (LISH.SYS_C0014665) violated - parent key not found
A foreign key will also prohibit changes made to primary key values which would invalidate the relationship. For example, we cannot update a primary key value that is referenced by a foreign key.
UPDATE continent
SET cont_id = 99999
WHERE cont_id = 3;
-- SQL Error: ORA-02292: integrity constraint (LISH.SYS_C0014683) violated - child record found
Or drop a table without removing all the child rows first.
DROP TABLE continent;
-- SQL Error: ORA-02449: unique/primary keys in table referenced by foreign keys

Optional Foreign Keys

A foreign key column can be nullable, making the relationship between parent and child optional.

For example.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NULL REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);
A row in the child table can exist with no reference to the parent, but if the foreign key column contains a value, it must be a valid one.
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, null, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');

UPDATE country
SET cont_id = 999999;
-- SQL Error: ORA-02291: integrity constraint (LISH.SYS_C0014702) violated - parent key not found
Business rules will dictate if this is valid for the application. If, for example, countries can be added to the database before the owning continent is known, then we have little choice but to make the foreign key constraint optional.

On Delete

We have seen that the default behavior for a foreign key constraint is to throw an error if an attempt is made to delete a parent which is still referenced by child rows.

However, the ON DELETE clause allows us to specify two alternatives.
  • CASCADE removes dependent child rows.
  • SET NULL sets foreign key values on dependent child rows to null.
First, using ON DELETE CASCADE:
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL REFERENCES continent ON DELETE CASCADE,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(122, 3, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');

SELECT ctry_name, cont_id
FROM country;
CTRY_NAME CONT_ID
Germany 3
Greece 3
Georgia 3

Now we delete the continent and select the COUNTRY rows again.
DELETE FROM continent WHERE cont_id = 3;

SELECT ctry_name, cont_id
FROM country;
-- 0 rows selected
Alternatively we can use the ON DELETE SET NULL option.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NULL REFERENCES continent ON DELETE SET NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(122, 3, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');

SELECT ctry_name, cont_id
FROM country;
CTRY_NAME CONT_ID
Germany 3
Greece 3
Georgia 3

Now we delete the continent and select the COUNTRY rows again.
DELETE FROM continent
WHERE cont_id = 3;

SELECT ctry_name, cont_id
FROM country;
CTRY_NAME CONT_ID
Germany (null)
Greece (null)
Georgia (null)

Obviously, the foreign key column must be nullable for this to work.
»