locked
Compound Primary Key - Does each element need a default value? And if so, what? RRS feed

  • Question

  • I know that Primary Key columns cannot be null. Do they need a default value? And if so, what? An empty string? Or what in the case of numbers? The reason I ask is that I am getting warnings in Red Gate SQL Compare when trying to deploy table changes:

    The column on table is being marked as NOT NULL. There is no default value so the update may fail.

    Is there a "Best practices" list when it comes to creating primary keys (especially compound ones)?

    Wednesday, September 26, 2012 3:10 PM

Answers

  • This is just a warning; it may or may not make sense to have default on one or more of the attributes in the key. For example, the classic two-column key is (longitude, latitude); what would the default for either of these values be? They are equally strong. But if I have a (genus, species) key then a default species of "to be determined" might make sense when I get a new genus. They are in a hierarchy and species is subordinate. 


    --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

    • Proposed as answer by Naomi N Thursday, September 27, 2012 2:30 AM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 8:22 PM
    Thursday, September 27, 2012 2:10 AM

All replies

  • You normally don't have compound primary keys where single columns can be null and thus need a default value. There maybe some obscure case were you need them, but I would consider it to be flaw in the database model in the first place.

    Take a closer look at that update. This message should indicate, that the update may fail when there is already data in the table. For these cases you need migration scripts..

    Wednesday, September 26, 2012 3:57 PM
  • I know that Primary Key columns cannot be null. Do they need a default value? And if so, what? An empty string? Or what in the case of numbers? The reason I ask is that I am getting warnings in Red Gate SQL Compare when trying to deploy table changes:

    Are you changing an existing column to not null?  That may explain the warning, since existing NULL values will need a value.  That can be done with an UPDATE beforehand or via a default constraint when the column is changed.  A default constraint is not required to assign values to new rows after the primary key constraint is added.  The value can be specified via the app code, an identity, or default constraint.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, September 26, 2012 6:17 PM
  • I don't see that using compound key as a Primary key is a good practice. When you add a Primary Key, by default, a Clustered Index is created on the table. Use the Primary key if it is required on a column which is best suited for this.

    However, using default value or replacing all the NULLs with any empty string will not help to introduce a Primary key as duplicate values are not allowed in the Primary key. But yes, if you want to add a Compound Primary key and the compound key will be unique, then you can do it. In this case, update your NULL records with a empty string or other default value as per project requirement and try to add Primary Key. Default value won't help in the case of Primary key as it required distinct values.

    Yogesh, http://www.sqlservertalks.com/

    Wednesday, September 26, 2012 6:54 PM
  • This is just a warning; it may or may not make sense to have default on one or more of the attributes in the key. For example, the classic two-column key is (longitude, latitude); what would the default for either of these values be? They are equally strong. But if I have a (genus, species) key then a default species of "to be determined" might make sense when I get a new genus. They are in a hierarchy and species is subordinate. 


    --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

    • Proposed as answer by Naomi N Thursday, September 27, 2012 2:30 AM
    • Marked as answer by Kalman Toth Monday, October 1, 2012 8:22 PM
    Thursday, September 27, 2012 2:10 AM
  • Is there a "Best practices" list when it comes to creating primary keys (especially compound ones)?

    Don't use composite PRIMARY KEYs. That is only for top-top database design experts.

    For the rest of us there is a splendid solution,  SURROGATE IDENTITY PRIMARY KEY:

    CREATE TABLE Celebrity (
    Celebrity ID INT IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(64) UNIQUE,
    BirthDate .....

    Table design examples: http://www.sqlusa.com/bestpractices2005/bankdatabase/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012





    • Edited by Kalman Toth Monday, October 1, 2012 8:31 PM
    Monday, October 1, 2012 8:27 PM