DELETE problems: reference

Cannot DELETE because of reference.

schema:scott

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'

Referential integrity will be respected if the tables were created with type INNODB. This will not always be the case.

We can examine this constraint to find the table that has it...

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')

INSERT and DELETE Operations

INSERT new records

UPDATE existing records

DELETE records

INSERT ... SELECT

INSERT: Not all fields need to be specified

INSERT a date

Explicitly enter a NULL

INSERT problems: reference

DELETE problems: reference

INSERT problems: incompatible formats

String contains a quote '

Data normalization