Re: Foreign Key can't refer to one of 2 primary keys

On Saturday, June 21, 2003, at 05:04 PM, Reuben D. Budiardja wrote:

> Hi all,
> Suppose I have a table with more than one primary key. If I create
> another
> table and I want one of the column of that second table REFERENCE to
> one of
> the primary key of the first table, how do I do that?
>
> eg
> CREATE TABLE test
> (
> col1 VARCHAR(20),
> col2 VARCHAR(20),
> col3 VARCHAR(20),
> PRIMARY KEY (col1,col2,col3)
> );

What you've done above is create a table with one primary key that is a
composite key (it is composed of three columns, none of which need to
individually be unique and not null). You can only have one primary key
in a table. A primary key is one that is designated as a primary key
and has the following constraints -- unique not null. You can have
several candidate keys -- these are columns with the constraints unique
and not null. However, only one candidate key can be designated as the
primary key.

>
> CREATE TABLE myforeign
> (
> mycol1 VARCHAR(20) REFERENCES test,
> mycol2 VARCHAR(25),
> PRIMARY KEY(mycol1)
> );
>

In the above, you need to explicitly name the primary key column in the
referenced table. If you are going to create a foreign key on a
composite primary key you need to do an express foreign declaration --

CREATE TABLE test (
col1 varchar(20),
col2 varchar(20),
col3 varchar(20),
col4 varchar(20),
PRIMARY KEY (col1, col2, col3)
);

CREATE TABLE myforeign (
mycol1 varchar(25),
mycol2 varchar(20),
mycol3 varchar(20),
mycol4 varchar(20),
mycol5 varchar(20),
PRIMARY KEY (mycol1),
FOREIGN KEY (mycol2, mycol3, mycol4) REFERENCES test (col1, col2, col3)
);

See the user's guide in the postgresql documentation (Sections 2.4.3
and 2.4.4).

> [snip]