noobquestion: How does Postgres delete 'smart' when deleting with FK contraints?

[Erwin Moller <erwin@xxxxxxxxxx>]

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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general