Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Unique constraint on nullable column

    I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance.

    Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better.

    First, write a trigger.

    Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record.

    EXAMPLE DML:
    CREATE TABLE test (
    ssn CHAR(9) NULL,
    testId INT identity(1,1) NOT NULL,
    ComputedConstraint AS

    CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9))
    ELSE ssn
    END, UNIQUE (ComputedConstraint))

    Any comments would be greatly appreciated.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Oh yeah, Brett please no comments about the design. Unfortunately at this point I'm stuck with it.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by peterlemonjello
    Oh yeah, Brett please no comments about the design. Unfortunately at this point I'm stuck with it.

    Hey!

    I think that's very slick...I would wonder though when you would hit a dup key situation...

    Why not expand the size to be varchar(25) and use a datetime value instead...

    Seem a lot like a non identifying relationship....too bad sql server doesn't allow more than 1 null for a unique key like DB2....maybe in Yukon...

    You know....I think I like a trigger to enforce uniqueness, and create a non clustered non unique index on the ssn column and not use the computed column...

    1. It's more flexible and 2. You don't get "made up" keys...

    How would you know to reference them in the future anyway?

    Sorry


    Is it 5:00 yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Your right, I should change the computed column to varchar(36) and CAST(newid() as varchar(36)) instead of using the pk identity value if the SSN is null or multiply the identity * -1 to make it negative. I've seen problems with duplicates using datetimes in the past.

    I'm confused by your reference question. The computed column should never be referenced. It's only there to check uniqueness without using a trigger. Does this answer you question?
    Last edited by peterlemonjello; 07-16-04 at 13:12.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I'm confused...so what else is new...

    What does the computed column by you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    All it buys is enforcing uniqueness on a nullable column without using a trigger. You know it's one of those 10 different ways to skin a cat.

  7. #7
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Another way is to make it a varchar(50) field with a default of NewID(). Seems to work here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •