Thread: noobquestion: How does Postgres delete 'smart' when deleting with FK contraints?
noobquestion: How does Postgres delete 'smart' when deleting with FK contraints?
From
Erwin Moller
Date:
Hi group, Consider the following simplified table: create table tbltest( testid INTEGER PRIMARY KEY, reftotestid INTEGER REFERENCES tbltest(testid), langid INTEGER ); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (1,NULL,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (2,NULL,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (3,2,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (4,3,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (5,1,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (6,2,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (7,3,4); INSERT INTO tbltest (testid,reftotestid,langid) VALUES (8,4,4); Now, if I give the command: delete from tbltest where (testid=1); I get the error: ERROR: update or delete on "tbltest" violates foreign key constraint "tbltest_reftotestid_fkey" on "tbltest" That makes total sense of course. the row with testid which holds 1 is still referenced by other rows. But when I give this command: delete from tbltest where (langid=4); Postgres just deletes them all. That suprised me a little. I expected, for no particular reason, that postgres would just start deleting the records that fit the criteria (in this case all) in some 'random' order. So I expected Postgres would hit a FK contraint. But my Postgresql (8.1) is smart enough to do it anyway. :-) Now I am curious, can anybody explain to me how Postgresql pulls that trick? Thanks for your time. Regards, Erwin Moller