How to consider one key among composite primary keys in a table as a foreign key in a another table-CodePudding

I am new to postgres i am creating a table with multiple primary keys

CREATE TABLE tab1(
col_1 int,
col_2 txt,
col_3 txt,
PRIMARY KEY (col_1,col_2)
);

I am trying refer one of the primary in table "tabl" as foreign key in another table

CREATE TABLE tab2 (
col_4 int,
col_5 txt,
col_6 numeric(5,2),
PRIMAY KEY (col_4),
CONSTRAINT fk_tab1 FOREIGN KEY(col_5) REFERENCES tab1(col_1) ON DELETE SET NULL
);

But when i am trying to do this i am getting error as below

ERROR:  there is no unique constraint matching given keys for referenced table "tab1"
SQL state: 42830

How can i take one key among multiple primary keys in one table as foreign key in another table.

CodePudding user response：

Your primary key (PK) mentions two columns. PKs enforce uniqueness, and multicolumn primary keys enforce uniqueness on the combination of the two columns. Example:

col_1       col_2
1           Michael         OK
2           Stonebraker     OK
3           Michael         OK
2           Stonebraker     NOT ALLOWED -- duplicate PK values

If you want your col_1 and col_2 columns to separately contain unique values -- unique UserIds and unique names for example -- define your PK on just one of them and a UNIQUE key on the other. If you do that, you can reference either column as a foreign key (FK).

And, when you want to make an outbound FK reference from tab2 to a column in tab1, you must define that column in that table as a single-column PK or a UNIQUE key. Maybe like this:

CREATE TABLE tab1(
  col_1 int PRIMARY KEY,
  col_2 text UNIQUE,
  col_3 text
);

But be careful making text columns UNIQUE. They can be very very long, and enforcing uniqueness on vast amounts of text can cause performance trouble.