In postgresql, how to prevent duplicates on a table based on a column from a different table

Question

I have an item table containing some items. Items are grouped into categories:

CREATE TABLE category (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE item (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    category_id integer NOT NULL REFERENCES category (id)
);

For some categories I want to make one item as the "choice of the week":

CREATE TABLE category_choice_of_the_week (
    item_id integer UNIQUE NOT NULL REFERENCES item (id)
);

This is working fine, for example I can query the choice of the week for a given category using:

SELECT * FROM item WHERE item.category_id = 1
    AND item.id IN (SELECT item_id FROM category_choice_of_the_week);

However there is no protection from declaring multiple items as choices of the week for one single category.

The question is how to add a constraint (or index) to prevent inserting duplicate entries in a table (the choices table) based on uniqueness of a field (category_id) declared in another table (item table).

score 1 · Answer 1 · answered Jan 24 '21 at 21:50

You want to declare the tables as using a category in item of the week, included in the foreign key constraint:

create unique index unq_item_category_item on items(category_id, id);

Then for category choice:

alter table category_choice add constraint fk_category_choice_item
    foreign key (category_id, item_id) references items(category_id, id);

Then the unique constraint:

alter table category_choice add constraint unq_category_choice_category
    unique (category);

Of course, you can include a second column such as week in the unique constraint.

score 0 · Answer 2 · answered Jan 24 '21 at 19:54

0

As structured this requires a trigger as it is not possible to write a constraint other than a foreign key on another table.

This similar query (https://stackoverflow.com/a/27107470/14922310) has a good explanation for that route.

However I would suggest that you define the category of the week table with the columns week_num, category, item and then you can enforce a unique constraint on (week_num, category) so that you can only have one item of the week for each category and week.

answered Jan 24 '21 at 19:54

BossGman

58
5

Thanks. I mentioned "week" to try to make the example a little realistic, please ignore it as it is not relevant to my question (consider the table name as `category_choice`). Regarding the addition of a `category` column, this will prevent the mentioned duplication, however it will introduce another issue where someone can insert an item with an arbitrary category that is not equal to the item category as declared in the `item` table. – Ejez Jan 24 '21 at 20:19

score 0 · Accepted Answer · answered Jan 25 '21 at 06:27

Based on Gordon's answer, I have the following:

CREATE TABLE category (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE item (
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    category_id integer NOT NULL REFERENCES category (id),
    -- The following "unique" constraint is necessary to be able to reference
    -- (category_id, id) in a foreign key of another table.
    UNIQUE (category_id, id)
);

CREATE TABLE category_choice_of_the_week (
    -- The information about the category of the item is embedded in the
    -- "item_id" field, as each item belongs to one category as declared in the
    -- "item" table, however adding an explicit "category_id" field allows
    -- enforcing the uniqueness of the category in an easy way.
    category_id integer UNIQUE NOT NULL,
    item_id integer UNIQUE NOT NULL,
    -- Adding a foreign key constraint for the combination of the above fields
    -- instead of separate foreign key constraint for each field, guards against
    -- inserting an item with an arbitrary category that is not equal to that
    -- item category. However for this to work, postgresql requires us to add a
    -- "unique" constraint for the referenced combination in the "item" table.
    FOREIGN KEY (category_id, item_id) REFERENCES item (category_id, id)
);

In postgresql, how to prevent duplicates on a table based on a column from a different table

3 Answers3