Re: [HACKERS] FOREIGN KEYs (Was: Benchmarking PGSQL against Microsoft SQL 7)

Bruce Momjian wrote:
>
> > 2) Will PostgreSQL _ever_ have support for foreign keys? That is the
> > single biggest gripe around here at MindSpring. Lots of people use psql
> > and love it, myself included, but the lack of support for foreign keys
> > is so annoying that it boggles the mind. I know that you can simulate
> > the behaviour using triggers, and so the question comes, why can't
> > someone just hack the command syntax to translate the "foreign keys"
> > directive into the appropriate trigger insertion? This has been on the
> > TODO list for as long as I have been watching PostgreSQL, and at this
> > point I despair that it will ever get done. I would be happier if the
> > README just said "PostgreSQL does not and will never support the SQL
> > 'FOREIGN KEYS' directive."
>
> I was going to soapbox this item, row reuse without vacuum, and buffered
> logging as things at we really need. I am thinking about row reuse now,
> and want to post something about buffered logging this week to Vadim.
>
> That leaves foreign keys. We really need to address this, because it
> comes up so often. Can we hash through the issues again, and see if
> someone takes on the project.

If my memory servers me right:

Vadim _almost_ did implement FOREIGN KEY syntax just before 6.4 (or
6.4.1 ?)
using the trigger version in contrib.

But he stopped the work as he was unhappy with the way the contrib stuff
worked i.e. the keys can't break the rules even _during_ the statement.

I wholly agree with this being broken, but it still would not hurt very
because the same is unfortunately true for current primary keys/unique
indexes, try for example

hannu=> create table test(
hannu-> k int primary key,
hannu-> d int
hannu-> );
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index test_pkey
for table test
CREATE
hannu=> insert into test values(1,1);
INSERT 18285 1
hannu=> insert into test values(2,2);
INSERT 18286 1
hannu=> update test set k=k-1;
UPDATE 2
hannu=> update test set k=k+1;
ERROR: Cannot insert a duplicate key into a unique index

IMHO the right behaviour would be to allow this kind of updates, and I
suspect that Oracle does allow it.

At least the current behaviour is non-intuitive.

---------------
Hannu Krosing