2

I am not even sure how to word the question. Here is an explanation of what I am trying to accomplish:

I need to add a constraint on the AccountId and isDefaultWebOrderingPayment columns, that will allow for many AccountId and IsDefaultWebOrderingPayment when IsDefault is false, but will not allow more than one combination of AccountId and IsDefault true.

enter image description here

Fabio S.
  • 460
  • 7
  • 22
  • 1
    You need a unique filtered index probably. – Martin Smith Oct 09 '17 at 05:10
  • 1
    Possible duplicate of [SQL can I have a "conditionally unique" constraint on a table?](https://stackoverflow.com/questions/3283347/sql-can-i-have-a-conditionally-unique-constraint-on-a-table) – Alex Oct 09 '17 at 05:18

1 Answers1

3

Based on Martin Smith comment, creating a unique index did the trick.

CREATE UNIQUE NONCLUSTERED INDEX uq_AccountId_IsDefaultWebOrderingPayment 
ON PaymentOptions(AccountId, IsDefaultWebOrderingPayment) 
WHERE AccountId IS NOT NULL AND IsDefaultWebOrderingPayment = 1 
GO
Fabio S.
  • 460
  • 7
  • 22