2

Possible Duplicate:
Unique Constraint, excluding NULL values

Lets say i have a table with addresses, and i want an owner to have only One main address. Fist do i define a index/key or a check constraint? What's the expression for this?

TABLE_Address
--OwnerId
--IsMain    (not null able)
--City etc....

So i need a Unique combination of ( IsMain ==true AND ownerId)

Community
  • 1
  • 1
Anestis Kivranoglou
  • 7,728
  • 5
  • 44
  • 47
  • 1
    Please state version of SQL Server as answer varies. If 2008+ you can use a filtered index and if 2000/2005 an indexed view. – Martin Smith Dec 15 '12 at 18:55
  • It's SQL Server 2012. Do filtered indexes require that all other values except the unique one to be Null? – Anestis Kivranoglou Dec 16 '12 at 17:59
  • 1
    Ah sorry I thought I had chosen a duplicate that had example syntax for both. For filtered indexes you just need `CREATE UNIQUE INDEX ix ON TABLE_Address(ownerId) WHERE IsMain=1` – Martin Smith Dec 16 '12 at 21:49
  • This statement also restricts the IsMain=false value into unique, so i can't have multiple non-main addresses...... Is this supposed to work with Null values only? – Anestis Kivranoglou Dec 16 '12 at 22:59
  • 1
    No it doesn't restrict IsMain=false to be unique as they aren't included in the filtered index at all. They are excluded by the `WHERE` clause. SQL Server doesn't have a boolean datatype and `false`. What datatype are you actually using? I assumed `bit` and therefore `1` or `0` – Martin Smith Dec 16 '12 at 23:04
  • Yes my mistake forgot to delete the previous restriction ty! – Anestis Kivranoglou Dec 17 '12 at 01:05

1 Answers1

1

Create a filtered unique index on ownerId with condition IsMain = 1. This index will a) enforce this condition and b) help your application to the pre-insert validation query.

usr
  • 168,620
  • 35
  • 240
  • 369