Date: Fri, 2 Sep 2005 16:43:15 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Jerome Alet <alet@librelogiciel.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: not sure about constraints
In-Reply-To: <20050902132356.GD10284@mail.librelogiciel.com>
Message-ID: <Pine.LNX.4.44.0509021626160.8396-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

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