July 31, 2009 at 11:21 pm
I've been reading online and I see a lot of people have had problems with implementing unique constraints in MS SQL Server 2005 for a column which could potentially contain nulls.
I currently have two columns where I want to put a unique constraint. None of these 2 columns is a primary key of the table. I want to be able to add a constraint on a varchar column which will always have a unique value but not an identity column. My second column I want to add a unique constraint on is a date column.
When data is inserted from a view into my table with these 2 constraint columns and more, all the dates are initially set to NULL. After having implemented my unique constraints on my table, I hit an error which is below:
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'tblcountryoforigin_archivedate_uq'. Cannot insert duplicate key in object 'dbo.tblCountryOfOrigin'.
The statement has been terminated.
It seems like I get that error because I have multiple NULL values in the "archiveDate" column which has a constraint on it. After reading online, I see that if unique constraints have been implemented there only one NULL is allowed. I'm not sure if that is once per record, or once in the table with the constraint.
So if someone can offer an explanation but also tell me what would be a good strategy you would use to implement constraints so that the values within these 2 columns are always preserved.
Thanks,
S
--
:hehe:
August 1, 2009 at 2:18 pm
SQL Server is doing what you have asked for which is, enforce a unique key constraint.
You want to define a unique key constraint when you do not want to have more than one row with a specific value for constrained column in that table -that's why it's called "unique key constraint".
Strategy? do not attempt to insert a duplicate key and SQL Server will be happy.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 1, 2009 at 4:17 pm
Slick84
When data is inserted from a view into my table with these 2 constraint columns and more, all the dates are initially set to NULL
'tblcountryoforigin_archivedate_uq
Not knowing the use of the date column my questions might be totally non-sensical. But I have to ask.
a). What does the data in the offending column represent?
b). Why must it be unique?.
c). values within these 2 columns are always preserved? ? ?
If it is the date of the entry into the table then would a default value such as GETDATE() satisfy your needs, or would some other default value be sufficient?
September 9, 2009 at 10:09 am
Ok, so I realize that in a perfect world you shouldn't have nulls in a column you want unique, but since I neither work nor live in such a place, what's the best way to handle the situation where you want a column to be nullable, but when there is data you want it to be unique?
It's easy enough to write a before trigger to check for uniqueness when not null, or I could write a UDF and tie it to a check constraint and basically do the same. I personally would prefer the trigger but I'm wondering if there's an advantage to the latter or another alternative that is even better.
Thanks
September 9, 2009 at 10:56 am
jimk (9/9/2009)
what's the best way to handle the situation where you want a column to be nullable, but when there is data you want it to be unique?
Something is wrong with data modeling - one of rdbms pilars is not to allow null values on an unique column.
I would suggest to go back to the drawing board, chances are current data modeling strategy will back fire - big time - in the future.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 11:49 am
Hey Paul, thanks a lot for the response, but I think you're missing my point. I'm well aware of the fact that having a pure design would be ideal. I don't have that luxury at the moment. I wasn't involved in the initial design which resulted in the current situation, and I'm not in a position to refactor everything just to have a completely normalized design... so telling me to go back to the drawing board is not exactly helpful. What I'd like to know is, given the current design, is there a "best" way to handle it? Kinda like when you get a flat tire and the shop doesn't have a tire that fits. You can either wait around for a week while they order a new one, or you can plug the hole and move on.
Thanks
September 9, 2009 at 12:26 pm
jimk (9/9/2009)
Kinda like when you get a flat tire and the shop doesn't have a tire that fits. You can either wait around for a week while they order a new one, or you can plug the hole and move on.
If offending table gets inserts from a single procedure, add a previous select to check if value already exists.
If not sure, check this sort of uniqueness-when-not-null condition relying on a trigger.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply