DELETE problems: reference
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'
SELECT constraint_name, table_name, status FROM user_constraints WHERE r_constraint_name IN ( SELECT constraint_name FROM user_constraints WHERE table_name ='T_DEPT')