Post reply

Modifying table columns properties for Null and Unique constraints

  • January 28, 2009 at 7:12 am

    #155189

    Is it possible have the field set to not allow duplicates entries for the barcode number and ignore the nulls?

    Here is what I thought would work: Changing the Data Type from NVARCHAR to UNIQUEIDENTIFIER with the “ALLOW NULLS” option turned on.

    But will this happen: In SQL Server, if a UNIQUE constraint is defined upon a NULLABLE column, only one NULL value will be allowed in that column. It makes sense to me that the column should allow more than one NULL value because NULL values are distinct values. This is something to consider when you are defining your table structure and constraints.

  • January 28, 2009 at 8:44 am

    #933875

    You're dead right if you prevent duplicates you can only have one null in the table. Is there any other way you could make it unique - how about using an additional field and only populating it if the other field is null, maybe an identity field that is copied to this field only if the other value is null - Messy though!

    Andy

  • January 28, 2009 at 9:08 am

    #933903

    Another option is to pull those distinct values into their own table, basically a subset table, where the UNIQUEIDENTIFIER can be your primary key. Then you would have a foreign key relationship with the original table and allow nulls there making it an optional relationship. By doing that you also can move any column that only relate to data where that column is not null to the new table. It would be a more normalized design. Of course, this is stated without knowing much about the original database design, but this a technique that I have used in the past.

    Hope this helps,

    Greg

  • January 28, 2009 at 9:29 am

    #933924

    No, the UNIQUE constraint will only allow for 1 of any value, including NULL. Whether the column is uniqueidentifier or varchar doesn't matter, except you'll probably eat up more space with the uniqueidentifier.

    There's a couple of work arounds that pop to mind, but nothing that I would try one a really busy table. One idea is to do a check on the insert proc prior to executing the insert, and return an error message if the value is not null and already exists.

    Regards,

    Michelle Ufford
    SQLFool.com - Adventures in SQL Tuning

  • January 28, 2009 at 11:04 am

    #934016

    Thanks for the quick response. I will try this out and let you know how it went.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply