CODEWITHSUNDEEP
sqldatabaseduplicatesunique-key
blerrr
blerrr

Reputation: 51

2 unique key with one of them possible duplicate

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 ?

Upvotes: 0

Views: 101

Answers (2)

Manish
Manish

Reputation: 939

Best way would be to have a primary key. You can have a composite primary key on orderID and trans_date which would allow you to have more than one trans_date of same value as @Gordon has suggested.

If, for some reason, composite primary key doesn't suit your purpose you can have a trigger on the table that check all the conditions you want and allow the transaction to proceed further or not.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

Related Questions