March 15, 2002 at 2:33 pm
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!
March 15, 2002 at 2:36 pm
Oops, I hit "post" twice. Sorry about that . . .
March 15, 2002 at 2:38 pm
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)
March 18, 2002 at 3:37 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply