none
NULL in primary key

    Question

  • I have table with only 3 columns: col1, col2 and col3.
    All 3 columns are FK to some other tables, but col3 could be null.

    Now i can't put PK on this 3 columns (col1+col2 are not unique).

    Should I put instead UNIQUE constraint on this 3 columns, which allows that col3 is null for one combination?
    But then table would be without PK. There is best practice that every table in MS SQL should have PK.

    If I put some default, when col3 is not defined(0 for example), then i have to add also row (0, 'UNKNOWN') in PK table, which I don't won't since it will be shown in all combo boxes in my application or some other places.

    Third option is to add surrogate key as PK(but I don't need it as FK relation in some other table) and UNIQUE non-clustered index on col1, col2, col3.

    What is the best practice in this situation?

    Wednesday, July 23, 2014 12:41 PM

All replies

  • I will use the surrogate key as PK for the table and  create a UNIQUE NONCLUSTERED constraint on col1, col2, col3.
    Wednesday, July 23, 2014 1:50 PM
    Moderator
  • You do not always need a primary key.

    The very function of such an entry is to allow you to uniquely identify any given row. If there will never been more than one instance of any given row, there's no purpose for a PK, as the three existing columns will uniquely identify it.

    What will these columns represent? Is it a simple lookup table?

    Given that you have a three column row, there's not really a good reason to exclude any column from your clustered index.

    Wednesday, July 23, 2014 2:17 PM
  • Yes, it is simple lookup table.

    Lets say you have userID, productID and colorID.
    User can buy product without specifying the color(colorID IS NULL) or with defined color of colorID from colors table(FK).
    He can buy the same product more than once, but it must be always different color(including without color).

    Currently i have removed PK and added UNIQUE NONCLUSTERED constraint on all 3 columns.

    I don't see the reason why I would add surrogate key as PK since this row is not FK on any other table.

    Wednesday, July 23, 2014 2:49 PM
  • So then there will never be duplicates anyway.

    Sounds like you need a logical barrier to prevent this

    if exists (select * from myTable where userID = 1 and product = 1 and color = 1)
    begin
     select 'You have already purchased that!'
    end

    Wednesday, July 23, 2014 2:52 PM
  • Yes, it is simple lookup table.

    Lets say you have userID, productID and colorID.
    User can buy product without specifying the color(colorID IS NULL) or with defined color of colorID from colors table(FK).
    He can buy the same product more than once, but it must be always different color(including without color).

    Currently i have removed PK and added UNIQUE NONCLUSTERED constraint on all 3 columns.

    That seems the correct choice to me.  The only thing I would change is to make it a UNIQUE CLUSTERED index.  Generally speaking you want a clustered index on almost all your tables.  If this is the only index on the table, I would make it CLUSTERED.

    Tom

    Wednesday, July 23, 2014 4:17 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums.

    >> I have table with only 3 columns: col1, col2 and col_3. All 3 columns are FK to some other tables, but col_3 could be NULL. <<

    Is this what you meant? 

    CREATE TABLE Foobar
    (col_1 INTEGER NOT NULL
      REFERENCES F1 (col_1),
     col_2 INTEGER NOT NULL
      REFERENCES F2 (col_2),
     col_3 INTEGER 
      REFERENCES F3 (col_3),
    UNIQUE (col_1, col_2, col_3));

    >> Should I put instead UNIQUE constraint on these 3 columns, which allows that col_3 is NULL for one combination? <<

    Yes, if that is your data model. You are asking us to read your mind to get specs. What does NULL mean in the data model? 

    >> But then table would be without PK. There is best practice that every table in MS SQL should have PK. <<

    Yes, so this is not a table! I would use a default value to avoid the NULLs. 

    >> If I put some default, when col_3 is not defined(0 for example), then I have to add also row (0, 'UNKNOWN') in PK table, which I don't won't since it will be shown in all combo boxes in my application or some other places. <<

    There are no comb boxes in SQL! That is a problem for the presentation layers and we database people do not care about it. 

    CREATE TABLE Foobar
    (col_1 INTEGER NOT NULL
      REFERENCES F1 (col_1),
     col_2 INTEGER NOT NULL
      REFERENCES F2 (col_2),
     col_3 INTEGER DEFAULT 0 NOT NULL
      REFERENCES F3 (col_3),
    PRIMARY KEY (col_1, col_2, col_3));


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 23, 2014 5:58 PM
  • I will use the surrogate key as PK for the table and create a UNIQUE NONCLUSTERED constraint on col1, col2, col3.

    This has my vote too. With the reservation that it may place the clustered index elsewhere.

    Some people suggested that a PK may not be necessary, but you never know what future has in stock. Also, a client program may find life easier to work with a non-NULL key.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 23, 2014 10:05 PM
  • Celko,

    I usually add some DDL, but didn't think it is necessary in this case. I apologize. You DDL is exactly what is the case.

    "What does NULL mean in the data model? "

    Well, NULL means that col3 is not defined. I can put some default value, but than FK constrain will raise error.

    If I add that default value also to PK table, than it will shows in all queries searching by that table. I won't risk that much. If i would develop from the start than i would do that.

     Erland, you would put surrogate key as PK just for sake of future (or that table has PK as stated in SQL best practice) even if i don't need it anywhere?

    Thursday, July 24, 2014 12:11 PM
  •  Erland, you would put surrogate key as PK just for sake of future (or that table has PK as stated in SQL best practice) even if i don't need it anywhere?

    Yup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 24, 2014 10:23 PM