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

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,
mycol2 VARCHAR(25),
PRIMARY KEY(mycol1)
);

This gave me

ERROR:  number of key attributes in referenced table must be equal to foreign
key
        Illegal FOREIGN KEY definition references "test"


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

Thanks.
--
Reuben D. Budiardja

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

> [snip]