NULL permitted in Primary Key – why and in which DBMS?

by

Suppose you have a primary key containing a nullable column Kn.

If you want to have a second row rejected on the ground that in that second row, Kn is null and the table already contains a row with Kn null, then you are actually requiring that the system would treat the comparison “row1.Kn = row2.Kn” as giving TRUE (because you somehow want the system to detect that the key values in those rows are indeed equal). However, this comparison boils down to the comparison “null = null”, and the standard already explicitly specifies that null doesn’t compare equal to anything, including itself.

To allow for what you want, would thus amount to SQL deviating from its own principles regarding the treatment of null. There are innumerable inconsistencies in SQL, but this particular one never got past the committee.

More Related Contents:

  1. 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]
  2. NoSql vs Relational database
  3. Relational table naming convention [closed]
  4. When I should use one to one relationship?
  5. How should international geographical addresses be stored in a relational database?
  6. Still Confused About Identifying vs. Non-Identifying Relationships
  7. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  8. Minimum no of tables that exists after decomposing relation R into 1NF?
  9. uncertainty in developing a database model
  10. Is storing a delimited list in a database column really that bad?
  11. Should each and every table have a primary key?
  12. What is the minimal proof that a database relation is not in BCNF?
  13. Database, Table and Column Naming Conventions? [closed]
  14. When and why are database joins expensive?
  15. Normalization: What does “repeating groups” mean?
  16. designing database to hold different metadata information
  17. Supertype-subtype database design
  18. Storing time-series data, relational or non?
  19. Database optimization orders
  20. In what way does denormalization improve database performance?
  21. Difference between a theta join, equijoin and natural join
  22. What’s wrong with nullable columns in composite primary keys?
  23. Best representation of an ordered list in a database?
  24. Composite Primary Keys : Good or Bad?
  25. have address columns in each table or an address table that is referenced by the other tables?
  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. Inventory management with stock options
  29. Derived account balance vs stored account balance for a simple bank account?
  30. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

Leave a Comment