Postgres primary key allowing collision

Question

The table should have behavior similar to the primary key(Item_id, Date, Status). However, multiple cancel status is allowed.

Item_id  |Date       |Status   
--------------------------------
1        |2017-01-01 |Cancelled   
1        |2017-01-01 |Cancelled   
1        |2017-01-01 |Completed

In this case, I will be able to insert:

('1', '2017-01-01', 'Cancelled')

And in this case, the query should return error instead:

('1', '2017-01-01', 'Completed')

Is there a way to use constraint or other implementation to achieve this goal?

Could you share the structure of the table? How it was created? — Matthieu Libeer, Sep 10 '18 at 01:02
The question is how to structure the table to achieve this behavior. — RoastDuck, Sep 10 '18 at 01:09
Well then https://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql — Matthieu Libeer, Sep 10 '18 at 01:11
Multiple 'cancel' status with same Item_id and Date should be allowed. — RoastDuck, Sep 10 '18 at 01:24
Hi. Did you give the right example data in your tables & inserts? Your example violates the PK, the first 2 rows are the same. You can't insert the row you say succeed, it's already there. Also, please clarify via post edits, not comments. And your comment "Multiple 'cancel' status with same Item_id and Date should be allowed" doesn't agree with your PK, which says only that all 3 columns must be unique. — philipxy, Sep 10 '18 at 03:48

Matthieu Libeer · Accepted Answer · 2018-09-10T12:38:29.277

What you are asking is self conflicting. A primary key is by definition unique, thus it's impossible to have several rows with the same tuple (Item_id, Date, Status) if the primary key is (Item_id, Date, Status). For example, there would be no way to distinguish 2 rows if they are both identified by the same tuple (1, 2018-01-01, 'canceled')

It looks like you want to enforce a single completed row per tuple (item_id, date), which is doable: see this question. You will need to add a primary key column to your table.

Postgres primary key allowing collision

1 Answers1