DELETE problems: reference

From SQLZoo
Jump to navigation Jump to search

Cannot DELETE because of reference.


If a foreign key is set up between two tables it may be that you cannot delete a record. In this case the table t_staff references the table t_dept - you cannot delete the department 'co' if a member of staff belongs to that department.

DROP TABLE t_staff;
DROP TABLE t_dept;
CREATE TABLE t_dept(id CHAR(2), fname VARCHAR(20),PRIMARY KEY(id));
CREATE TABLE t_staff(name VARCHAR(20),
                     dept CHAR(2),
                     PRIMARY KEY(name),
                     FOREIGN KEY(dept) REFERENCES t_dept(id));
INSERT INTO t_dept VALUES ('co','School of Computing');
INSERT INTO t_staff VALUES ('Andrew','co')
DELETE FROM t_dept WHERE id='co'