pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednot sure about constraints
3+ messages / 2 participants
[nested] [flat]
* not sure about constraints
@ 2005-09-02 13:23 Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jerome Alet @ 2005-09-02 13:23 UTC (permalink / raw)
To: pgsql-sql
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 INT4 REFERENCES tableB(id),
blahD TEXT,
CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb));
which looks superfluous to me.
???
Thanks in advance
Jerome Alet
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not sure about constraints
@ 2005-09-02 13:43 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleus Mantzios @ 2005-09-02 13:43 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: not sure about constraints
@ 2005-09-02 14:26 Jerome Alet <alet@librelogiciel.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jerome Alet @ 2005-09-02 14:26 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-02 14:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-02 13:23 not sure about constraints Jerome Alet <alet@librelogiciel.com>
2005-09-02 13:43 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-09-02 14:26 ` Jerome Alet <alet@librelogiciel.com>
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