2 Unique Key With One Of Them Possible Duplicate

- 1 answer

Hello I want to ask if it's possible to set 2 unique key and in some condition one of them can have duplicate but the other one is not.

The example is like this :

orderID          trans_date
   1          2016-02-25 01:00:00  
   1          2016-02-25 01:00:01
   2          2016-02-25 01:00:00

As you can see if I only make trans_date an unique key then it can be duplicate, it also the same as the orderID. So instead of create unique id for one of them, why dont we create for both of them. OrderID and trans_date can't be the same as the other later.

Is it possible ?



If you want two keys to be unique as a pair, then you can create a unique index:

create unique index unq_example_orderid_transdate on example(orderID, trans_date);

Or, you can create a unique constraint which does essentially the same thing.