From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Jerome Alet <alet@librelogiciel.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: not sure about constraints
Date: Fri, 2 Sep 2005 16:43:15 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0509021626160.8396-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <20050902132356.GD10284@mail.librelogiciel.com>
O Jerome Alet Ýãñáøå óôéò Sep 2, 2005 :
> Hello,
>
> I've got 4 tables :
>
> CREATE TABLE tableA (id SERIAL PRIMARY KEY NOT NULL,
> blahA TEXT);
>
> CREATE TABLE tableB (id SERIAL PRIMARY KEY NOT NULL,
> blahB TEXT);
>
> CREATE TABLE tableC (id SERIAL PRIMARY KEY NOT NULL,
> ida INT4 REFERENCES tableA(id),
> idb INT4 REFERENCES tableB(id),
> blahC TEXT);
>
> CREATE TABLE tableD
> (id SERIAL PRIMARY KEY NOT NULL,
> ida INT4,
> idb INT4,
> blahC TEXT,
> CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb));
The above statement is in error.
In order to create FK to another table (tableC), you must do this
on tableC's PK (id) or some UNIQUE key in general.
>
> Is the definition of tableD sufficient, or should I do it this way
> instead :
>
> CREATE TABLE tableD
> (id SERIAL PRIMARY KEY NOT NULL,
> ida INT4 REFERENCES tableA(id),
> idb INT4 REFERENCES tableB(id),
> blahD TEXT,
> CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb));
>
Provided you have done something like
ALTER TABLE tablec add CONSTRAINT tablec_ukey UNIQUE (ida,idb);
Which means that each compination of the pair (ida,idb)
in tablec is unique in tablec,
i.e. NO 2 rows of tablec have the same (ida,idb),
Then you can go which chioce 1), since it is guaranteed
that ida belongs to tablea, and idb belongs to tableb
by tablec's contraints.
So the extra FKs defined in choice 2) are indeed reduntant.
But the point here is to understand, that always
when we point to another table, we point at some
Unique key of that table.
> which looks superfluous to me.
>
> ???
>
> Thanks in advance
>
> Jerome Alet
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
-Achilleus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, alet@librelogiciel.com
Subject: Re: not sure about constraints
In-Reply-To: <Pine.LNX.4.44.0509021626160.8396-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox