Foreign Key Contraint Notes

Back to Geoffrey's PostgreSQL Notes

Setting up a constraint to prevent deletion of a foreign key

The following is an example demonstrating how to set up a foreign key constraint in PostgreSQL 7.0:

CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
  school_id   INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
  school_name VARCHAR(80) NOT NULL
);

CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
  student_id   INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
  student_name VARCHAR(80) NOT NULL,
  school_id    INT NOT NULL,
  CONSTRAINT school_exists
    FOREIGN KEY(school_id) REFERENCES school
    ON DELETE RESTRICT
);

INSERT INTO school (school_name) VALUES ('Alice''s School of Truck Driving');
INSERT INTO school (school_name) VALUES ('Bob''s School of Underwater Knitting');

INSERT INTO student (student_name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (student_name, school_id) VALUES ('Doug', 1);
INSERT INTO student (student_name, school_id) VALUES ('Ernie', 2);

Note the 'ON DELETE RESTRICT' which will prevent you from deleting a school if there is a student going to that school. First look at what's in the tables:

SELECT * FROM school;
SELECT * FROM student;

Now attempt to delete the school:

DELETE FROM school WHERE school_id = 1;

If you try the above, you should see 'ERROR: school_exists referential integrity violation - key in school still referenced from student' and notice that nothing was deleted.

DROP SEQUENCE school_id_seq;
DROP TABLE school;
DROP SEQUENCE student_id_seq;
DROP TABLE student;

Cascade & Update

Instead of 'ON DELETE RESTRICT' you could specify 'ON DELETE CASCADE'. This would allow the delete (instead of preventing it like in the example above), but it would 'cascade' the delete to the student table so that any students going to the school you deleted would also be deleted.

As well as the 'ON DELETE ...' clause you can also specify what is to happen on an update of the foreign key with either:

• ON UPDATE RESTRICT or
• ON UPDATE CASCADE

ON UPDATE RESTRICT would prevent UPDATE school SET school_id = 20 WHERE school_id = 1 from proceeding if there were any students with a school_id of 1.

You might be able to guess that ON UPDATE CASCADE would allow the UPDATE school SET school_id = 20 WHERE school_id = 1 to proceed, but it would also update the school_id field in the student table appropriately.

Foreign Keys where the field names are different

Consider the following table setup:

CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
  id   INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
  name VARCHAR(80) NOT NULL
);

CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
  id        INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
  name      VARCHAR(80) NOT NULL,
  school_id INT NOT NULL,
  CONSTRAINT school_exists
    FOREIGN KEY(school_id) REFERENCES school(id)
    ON DELETE RESTRICT
);

INSERT INTO school (name) VALUES ('Alice''s School of Truck Driving');
INSERT INTO school (name) VALUES ('Bob''s School of Underwater Knitting');

INSERT INTO student (name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (name, school_id) VALUES ('Doug', 1);
INSERT INTO student (name, school_id) VALUES ('Ernie', 2);

Note how the field names don't match (school.id as opposed to student.school_id); in this case we can put the field name in brackets after the table name.

As before, the following will fail:

DELETE FROM school WHERE id = 1;

Back to Geoffrey's PostgreSQL Notes