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?
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.