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

user13906258 0
Viewed 18

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.

postgresql
1 Answers

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.

O. Jones0
Related