October 4, 2007 at 9:09 am
I have a column which is NULLABLE.
When a new varchar value is inserted into the column, it has to be unique.
How do I go about this?
I can't create the unique constraint as it complains about the NULLs in the column.
For example.........
I have 2 rows with column A set to NULL
I insert row 3 with the value 'red'
I insert row 4 with the value 'blue'
I insert row 5 with the value 'red' - I need the rdbms to prohibit this!!
I insert row 6 with a NULL value - this is okay
Can this be done? A quick answer would really be appreciated
Many thanks
October 4, 2007 at 9:36 am
A NULL value is like any other value as far as the unique constraint is concerned, so only one row will be allowed with null in that column.
Greg
Greg
October 4, 2007 at 9:45 am
Thanks. So is there a way round it?
Any gurus out there got a simple solution for me?
October 4, 2007 at 10:16 am
I can think of three ideas off the top of my head:
1) get rid of the nulls
2) create an update/insert trigger to enforce the "uniqueness"
3) allow updates / inserts only via a stored proc and validate uniqueness there.
October 4, 2007 at 10:20 am
Your best bet would be to make sure ALL inserts and updates are done through stored procedures and then code the value check into the stored procedure. For example, in your insert SP, prior to inserting the row, check to make sure that column value is not already being used. Do the same for updates. If the update SP attempts to update that column's value, check the table's existing values prior to the update.
Another avenue you could go would be to add the logic into the insert/update trigger. You must be careful here to make sure your trigger is coded to work for multiple row insert/updates and you must also be careful as any logic you add into a trigger will lengthen the transaction. The longer the transaction, the longer it will block other processes from the same resources. Another down side to this approach is that if you find that a non-unique value is present in the trigger, you must rollback the entire transaction.
Either way, put an index on the column!!
October 4, 2007 at 10:31 am
Thanks for the advice guys,
I'm kinda new to this. Would someone be kind enough to provide me examples of what the trigger code would look like.
And/Or the Proc.
Many thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply