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

Поиск
Список
Период
Сортировка
От Erwin Moller
Тема noobquestion: How does Postgres delete 'smart' when deleting with FK contraints?
Дата
Msg-id 493F9E5E.2090803@darwine.nl
обсуждение исходный текст
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Peter Billen
Дата:
Сообщение: disallow updates on column or whole table
Следующее
От: Mark Morgan Lloyd
Дата:
Сообщение: Re: PostgreSQL and eval()