Post reply

primary key with null

  • July 13, 2017 at 4:00 pm

    #331805

    I need to create composite key which has 4 column, but one of the column contains null values, I don't have option to choose it , bcoz tats only key I can use it.

    and another table is join this one with one of the column to get data.

    any other way to work around?

  • July 13, 2017 at 4:31 pm

    #1950859

    Your primary key cannot contain nulls.  There is no way around that.

    You can join on a table that has no primary key with no problem though.  The hard part can be finding a way to uniquely identify a column so you can join the tables nicely without creating duplicates.

    Without seeing the table structure and sample data, it is very hard to offer advice.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • July 14, 2017 at 4:23 am

    #1950886

    You could create a calculated field which is a composite of the other fields making sure you use ISNULL() on the field that can be NULL to convert it to something like 0. You can then use this as your Primary Key. You would have to create any Foreign Keys in the same way. I would advise using a delimiter such as | between each field value so that values such as "20", "4" and "2", "04" do not cause incorrect duplicates to occur. For this to work the field must be PERSISTED and you will probably have to wrap the whole statement in an ISNULL function to satisfy the requirement of a Primary or Foreign Key.

  • July 14, 2017 at 4:40 am

    #1950888

    tim.ffitch - Friday, July 14, 2017 4:23 AM

    You could create a calculated field which is a composite of the other fields making sure you use ISNULL() on the field that can be NULL to convert it to something like 0. You can then use this as your Primary Key. You would have to create any Foreign Keys in the same way. I would advise using a delimiter such as | between each field value so that values such as "20", "4" and "2", "04" do not cause incorrect duplicates to occur. For this to work the field must be PERSISTED and you will probably have to wrap the whole statement in an ISNULL function to satisfy the requirement of a Primary or Foreign Key.

    To be honest, the OP would just be better off updating all the NULL values to a 0. If the item is also a Foreign Key, then create the ID 0 in your foreign table as well, with NULL values. Then ensure that NULL cannot be passed to your table, either by updating your INSERT/UPDATE statements, or perhaps use a trigger that changes the value of a passed NULL to 0 for that field.

    Note, however, that if a single composite key can have multiple NULLs for that field, this will not work, as it will create duplicates. So, for example, if the value 1,1,1,0 already exists, and then a record 1,1,1,NULL is passed, then the insert will fail, as it will be a duplicate 1,1,1,0.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • July 14, 2017 at 5:12 am

    #1950889

    Thom A - Friday, July 14, 2017 4:40 AM

    tim.ffitch - Friday, July 14, 2017 4:23 AM

    You could create a calculated field which is a composite of the other fields making sure you use ISNULL() on the field that can be NULL to convert it to something like 0. You can then use this as your Primary Key. You would have to create any Foreign Keys in the same way. I would advise using a delimiter such as | between each field value so that values such as "20", "4" and "2", "04" do not cause incorrect duplicates to occur. For this to work the field must be PERSISTED and you will probably have to wrap the whole statement in an ISNULL function to satisfy the requirement of a Primary or Foreign Key.

    To be honest, the OP would just be better off updating all the NULL values to a 0. If the item is also a Foreign Key, then create the ID 0 in your foreign table as well, with NULL values. Then ensure that NULL cannot be passed to your table, either by updating your INSERT/UPDATE statements, or perhaps use a trigger that changes the value of a passed NULL to 0 for that field.

    Note, however, that if a single composite key can have multiple NULLs for that field, this will not work, as it will create duplicates. So, for example, if the value 1,1,1,0 already exists, and then a record 1,1,1,NULL is passed, then the insert will fail, as it will be a duplicate 1,1,1,0.

    It depends on the source data, if it is coming from a database system such as KCML, that allows NULL in a primary key, then the remaining field value combinations must also be unique. I have had to use all sorts of tricks in the past. The most complex was importing data from a Case Sensitive database to a Case Insensitive database where duplicates do occur because of the case differences in the key fields. That was a challenge but I pulled it off.

  • July 14, 2017 at 6:57 am

    #1950922

    I'm thinking that this is actually a pretty easy problem to solve.  The use of a PK constraint says that you cannot include NULLable columns.  So..... use an AK (alternate key) instead.  An AK is a unique constraint just like a PK but it allows NULLs.  It's not always necessary to have a key (like a PK) that doesn't allow NULL columns.

    And remember that a PK <> Clustered Index.  That's just the default.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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