Thread: Strange reference problem

Hi, all

When I run this SQL script, I get this error at the end:

------------
ERROR:  UNIQUE constraint matching given keys for referenced table "swt" not found
------------

If you run the script, you will see that a unique constraint IS created on swt.  Am I missing a maturity constraint in the foreign key code?

Cheers...

MikeA

 

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************


Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> CREATE TABLE "swt" (
>     "id" varchar(10) not null,
>     "org" varchar(7) not null references "organisation"("id"),
>     "description" varchar not null,
>     primary key("id", "org")
> );

> DROP TABLE "poi";
> CREATE TABLE "poi" (
>     "id" varchar(15) not null,
>     "org" varchar(7) not null references "organisation"("id"),
>     "name" varchar not null,
>     primary key("id", "org")
> );

> DROP TABLE "tug";
> CREATE TABLE "tug" (
>     "id" varchar(21) not null primary key,
>     "description" varchar not null,
>     "id_swt" varchar(10) not null references "swt"("id"),
>     "swt_org" varchar(7) not null references "swt"("org"),
>     "id_poi" varchar(15) not null references "poi"("id"),
>     "poi_org" varchar(7) not null references "poi"("org")
> );

Those primary key declarations say that the combination of ID and ORG
together will be unique for each row of swt (ditto poi).  They do not
promise that either ID or ORG will be unique by itself --- but that's
what the references clauses require to be valid.  You probably want
to declare the references using a FOREIGN KEY clause that says that
the two-column pair id_swt, swt_org references the two-column pair
swt(id,org), and likewise for poi.  AFAIK that's the only way to
define a multi-column reference key.

            regards, tom lane

OK, I see.  The REFERENCES clause added to a column definition is a short
cut if you only have one column as the foreign key, similar to the PRIMARY
KEY clause, which you can add to a single column, but if you want a
multi-column primary key, then you have to do it at the end.

Thanks, Tom.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 18 January 2001 02:21
To: Michael Ansley
Cc: 'pgsql-general@postgresql.org '
Subject: Re: [GENERAL] Strange reference problem


Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:
> CREATE TABLE "swt" (
>     "id" varchar(10) not null,
>     "org" varchar(7) not null references "organisation"("id"),
>     "description" varchar not null,
>     primary key("id", "org")
> );

> DROP TABLE "poi";
> CREATE TABLE "poi" (
>     "id" varchar(15) not null,
>     "org" varchar(7) not null references "organisation"("id"),
>     "name" varchar not null,
>     primary key("id", "org")
> );

> DROP TABLE "tug";
> CREATE TABLE "tug" (
>     "id" varchar(21) not null primary key,
>     "description" varchar not null,
>     "id_swt" varchar(10) not null references "swt"("id"),
>     "swt_org" varchar(7) not null references "swt"("org"),
>     "id_poi" varchar(15) not null references "poi"("id"),
>     "poi_org" varchar(7) not null references "poi"("org")
> );

Those primary key declarations say that the combination of ID and ORG
together will be unique for each row of swt (ditto poi).  They do not
promise that either ID or ORG will be unique by itself --- but that's
what the references clauses require to be valid.  You probably want
to declare the references using a FOREIGN KEY clause that says that
the two-column pair id_swt, swt_org references the two-column pair
swt(id,org), and likewise for poi.  AFAIK that's the only way to
define a multi-column reference key.

            regards, tom lane


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************