What’s wrong with nullable columns in composite primary keys?

by

Primary keys are for uniquely identifying rows. This is done by comparing all parts of a key to the input.

Per definition, NULL cannot be part of a successful comparison. Even a comparison to itself (NULL = NULL) will fail. This means a key containing NULL would not work.

Additonally, NULL is allowed in a foreign key, to mark an optional relationship.(*) Allowing it in the PK as well would break this.

(*)A word of caution: Having nullable foreign keys is not clean relational database design.

If there are two entities A and B where A can optionally be related to B, the clean solution is to create a resolution table (let’s say AB). That table would link A with B: If there is a relationship then it would contain a record, if there isn’t then it would not.

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. Should each and every table have a primary key?
  3. Database development mistakes made by application developers [closed]
  4. Database, Table and Column Naming Conventions? [closed]
  5. Surrogate vs. natural/business keys [closed]
  6. How are super- and subtype relationships in ER diagrams represented as tables?
  7. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  8. Normalization: What does “repeating groups” mean?
  9. Difference between scaling horizontally and vertically for databases [closed]
  10. First name, middle name, last name. Why not Full Name?
  11. Relational table naming convention [closed]
  12. What are the design criteria for primary keys?
  13. When I should use one to one relationship?
  14. Why use a 1-to-1 relationship in database design?
  15. What is the optimal length for an email address in a database?
  16. Supertype-subtype database design
  17. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  18. Database optimization orders
  19. In what way does denormalization improve database performance?
  20. Best representation of an ordered list in a database?
  21. Alternatives to Entity-Attribute-Value (EAV)?
  22. have address columns in each table or an address table that is referenced by the other tables?
  23. Minimum no of tables that exists after decomposing relation R into 1NF?
  24. Limit an sqlite Table’s Maximum Number of Rows
  25. NULL permitted in Primary Key – why and in which DBMS?
  26. What are some best practices and “rules of thumb” for creating database indexes?
  27. When we need to use 1-to-1 relationship in database design?
  28. Need help in developing DB logic
  29. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  30. Database – Data Versioning [closed]

Leave a Comment