Hi,
we had a discussion here whether the following DELETE should work or not. My expactation was that this should work as it is a single statement and FK constraints are evaluated on statement level (unless they are defined as deferred)
Here is the table definition and the test data:
Code:
CREATE TABLE fk_test
(
id integer primary key,
name varchar(20),
parent_id integer
);
ALTER TABLE fk_test
ADD CONSTRAINT fk_test_parent FOREIGN KEY (parent_id)
REFERENCES fk_test(id);
INSERT INTO fk_test (id,name,parent_id) values (1,'root', null);
INSERT INTO fk_test (id,name,parent_id) values (2,'two, 1);
INSERT INTO fk_test (id,name,parent_id) values (3,'three', 2);
INSERT INTO fk_test (id,name,parent_id) values (4,'four', 1);
COMMIT;
And this is the delete statement
Code:
DELETE FROM fk_test WHERE id IN (1,2,3,4);
COMMIT;
Now, Firebird and MySQL fail with an error message, whereas "all others" (Postgres, Oracle, DB2, SQL Server, Sybase, Derby, H2) happily delete the rows.
We also tried to specify the ids in the IN clause in the correct order, just to test it. But that doesn't change anything (which is expected as the order in the IN clause should not have any meaning)
I do understand that we could simply send four individual DELETE statements in the correct order. But my question is not how to make this work (with MySQL or Firebird).
My question is more of a basic nature:
To my understanding this is a legal statement and should work, but as there are DBMS out there that do it differently (a minority though) it could well be something that is no clearly specified.
What are you thinking?
Should the statement work? (from a "relational" point of view, not from a SQL or DBMS specific point of view)
dBforums
>
Should this fail?
06-28-10, 03:30
Linear Mode
