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

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)
);

CREATE TABLE myforeign
(
mycol1 VARCHAR(20) REFERENCES test,

maybe:

mycol1 VARCHAR(20) REFERENCES test(col1)

But then you will need a unique index or constaint on col1, that would then make the primary key on test redundant with col2 and col3 !

I don't se any obvious reason why I cannot do that. Any help?

Just a guess.

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).