Re: 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