CODEWITHSUNDEEP
sqlsql-server
Freddie93
Freddie93

Reputation: 1

SQL constraint on existing table - unique combination of values only when another column is null

I have a pre-existing SQL Server table structured similarly to this:

[Username]   [Gateway]   [Code]   [ActiveTo]   [OrgId]  
------------------------------------------------------
user1        gateway      50        Null        100
user1        gateway      50        Null        101
user2        gateway2     51        Null        102
user3        gateway3     52       12/08/17     103

This table already has duplicate combinations of Username, gateway and code. I want to add a constraint such that any new users added have a unique combination of [Username], [Gateway] and [Code] but that ignores two pre-existing duplicates such as the two user1 entries above (not ignoring new entries that match pre-existing ones).

But I only want this to be applied when the duplicate's [ActiveTo] value is null (new entries have ActiveTo as null by default).

So applying the constraint to the table above would ignore the two user1 duplicates.

Then adding a row [user2], [gateway2], [52], [Null], [104] would be fine.

Then adding a row [user2], [gateway2], [51], [Null], [105] would throw an error.

But then adding row [user3], [gateway3], [52], [Null], [106] would be fine.

Any ideas?

Upvotes: 0

Views: 68

Answers (1)

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

You can use a unique index :

CREATE UNIQUE INDEX table_name_index_1 on table_name(Username, Gateway, Code) WHERE ActiveTo IS NOT NULL

Upvotes: 0

Related Questions