"Almost" unique constraints

• Raymond Kim

  Ten Centuries

  Points: 1350

  More actions

  March 15, 2002 at 2:33 pm

  #78412

  Is there a way to allow only *one* multiple value in an otherwise-unique column? In other words, is there a provision to allow for "exceptions" to a unique constraint?

  For example, let's say I have a data column filled with the following rows:

  Table

  Chair

  Lamp

  I *do* want to allow multiple instances of "chair," so the following would be allowed:

  Table

  Chair

  Chair

  Lamp

  Chair

  However, I *do NOT* want to allow multiple values of anything else; therefore, trying to enter "table" or "lamp" would result in an error.

  On a related note: I defined a unique column in a database, and I set it to allow NULLs. However, it's giving me errors because of multiple instances of NULL. Would this be a limitation of the question I stated above, or should a unique column allow multiple NULLs?

  Thanks in advance for your help!

• Raymond Kim

  Ten Centuries

  Points: 1350

  More actions

  March 15, 2002 at 2:36 pm

  #425485

  Oops, I hit "post" twice. Sorry about that . . .

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 15, 2002 at 2:38 pm

  #425486

  Not directly, however if you are using SQL 2000 you could build a function to check unique with exceptions.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• Deon

  SSChasing Mays

  Points: 636

  More actions

  March 18, 2002 at 3:37 am

  #425524

  This sounds more like you a business rule which I would have implemented through a data object. If this is not feasable or you need to rollback a transaction if a duplicate is inserted, then another way to do it would be through triggers.