April 24, 2002 at 12:18 pm
Folks,
I am trying to put a unique contraint or index on a column named "POLE_NUMBER". I made sure there were no duplicate entries in the column. I then created a new table and appended all of the rows that had NULL values in POLE_NUMBER. I then deleted the rows with a NULL value in POLE_NUMBER in my original table. After I created a UNIQUE INDEX on POLE_NUMBER. All worked great. I then went to append the NULLS back into the original table and the append failed.
It was my understanding that the UNIQUE constraint would allow NULLS, you just couldn't create the UNIQUE Index/constraint with NULLS.
I have since tried just entering in a new records with a NULL value, and again no luck. This column won't allow nulls.
What I have checked: The column is checked and NULLS ARE allowed. I have also tried this with the check constraint declared as opposed to the index. Although I'm not really sure why both are offered.
Any ideas?
Thanks!!
April 24, 2002 at 12:42 pm
From BOL
quote:
You want to enforce uniqueness in a column that allows null values. You can attach unique constraints to columns that allow null values, whereas you can attach primary key constraints only to columns that do not allow null values. When you attach a unique constraint to a column allowing null values, you ensure that at most one row will have a null value in the constrained column.
found under article title "Creating a Unique Constraint". Notes it states only one row can be null otherwise you break the unique rule.
If you need to have items unique but allow as many nulls as you want then you can do thru a trigger to check on insert/update that the value does not exist in the table when the insert table data is not null.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply