Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux