Thursday, October 09, 2008

DB2 lets you drop a parent table

DB2 is usually a rather strict database system: It doesn't allow you to drop a procedure which is being used by a function. It uses pessimistic locking. It typically forces you to back up a tablespace if you aggressively load data into a table if the database isn't using circular logging. Etc.

So I found it surprising that it allows you to drop a table which is being referred to in a foreign key. DB2 doesn't even warn you about the fact that the child table(s) have lost a potentially important constraint. That's evil.

I know of no other DBMS which lets you do drop a parent table: PostgreSQL refuses it (unless you add a CASCADE option to the DROP statement), MSSQL refuses it. Not even MySQL lets you do it.

As always, MySQL has little surprises:
child_id INT NOT NULL,
parent_id INT NOT NULL,
whatever VARCHAR(50) NOT NULL,
PRIMARY KEY(child_id,parent_id),
CONSTRAINT child_fk FOREIGN KEY (parent_id) REFERENCES parent
ERROR 1005 (HY000): Can't create table './test/child.frm' (errno: 150)

So what is the reason behind that confusing error message (which also qualifies as evil)? -- "REFERENCES parent" must be explicit: "REFERENCES parent(parent_id)"...

No comments: