Thread: not sure about constraints

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));                    
 
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
INT4REFERENCES tableB(id),    blahD TEXT,    CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb));
               
 
which looks superfluous to me.                     

???

Thanks in advance

Jerome Alet

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

On Fri, Sep 02, 2005 at 04:43:15PM +0300, Achilleus Mantzios wrote:
> O Jerome Alet ?????? ???? Sep 2, 2005 :
> 
> > 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.

sorry, while adapting the code for the purpose of posting here,
I've forgotten that I've this line in my real script :
 CREATE UNIQUE INDEX tableCindex ON tableC (ida, idb); 
So I think tableD should be OK (BTW I don't have any error)

Thanks for your help

bye

Jerome Alet