Postgres - foreign key on not unique column-CodePudding

I have the following table:

while my items table has the item_code column, I can have the same item_code multiple times for different store_id.

I do want to check on the DB level that my item_code exists in my items table, but when trying to do so, I get the following error:

[42830] ERROR: there is no unique constraint matching given keys for referenced table "items".

Indeed it's not unique, and I can't make it unique.

What I can do in this case to support foreign key check on item_code ?

CodePudding user response：

In your case this is not exactly a foreign key. I would suggest that you use a check constraint to make sure that item_code exists in items table, no matter in which store. As you can not do that directly (subqueries are not allowed in check conditions), you will need a simple helper function.

create function item_code_exists(ic bigint) returns boolean as
$$
 select exists (select from items where item_code = ic);
$$ language sql;

and then use a column definition in the other table DDL like this:

item_ref bigint check (item_code_exists(item_ref))

CodePudding user response：

You first have to create a UNIQUE or PRIMARY KEY constraint on the item_code column of items. A foreign key must reference a unique or primary key constraint.

Every table should have a primary key!