1

My work

  • A primary key is unique and only one contains for a table.

  • There are many unique keys that can contain in a table.

  • In SQL Server, the primary key is not Nullable. But, a unique key may contain only one null value.

My question:

Practically in SQL Server, the primary key is not nullable. But when it comes in theoretical, why don't we can have exactly only one Null value in the primary key when it allows a Null value in the unique key?

Thanks for answering my question

Sorry for asking a general question, after reading the answer I got a clear idea about null values in DB.

Also, I confused with programming languages like in C++, Jave, where the null value can be comparable. After doing some work, I understand that Null is not comparable in SQL.

In programming languages like in C++, Jave...

Null==Null 

returns TRUE

But, in SQL

Null==Null 

returns FALSE, because Null value is unknown and it could be any value.

2
  • NULL = NULL (or NULL = anything, actually) returns NULL instead of FALSE. A nuance, but it's important more often than you'd think. Jul 6 '20 at 23:01
  • NULL does not (SQL-)"equal" NULL in SQL but you can still "compare" 2 values in various ways where you get "true" when they are both NULL. Normal "equals" is expressed in standard SQL by IS NOT DISTINCT FROM.
    – philipxy
    Jul 9 '20 at 9:47
9

No attribute that is part of a key can contain nulls; if a column is nullable then it isn't part of any key. It is also the case that the set of columns defined by a UNIQUE or PRIMARY KEY constraint is NOT a key unless it is a minimal superkey and also non-nullable.

Why can't keys permit nulls? There are lots of potential reasons:

  1. Keys are a relational concept whereas nulls (as we know them today) are essentially a SQL invention which came along years later. SQL DBMSs are different to Relational DBMSs.
  2. Nulls are not values so they are not key values. Nulls are markers for unspecified values.
  3. Keys are supposed to be irreducible (minimal) - in other words all the components of the key are required to make a superkey. Since null means that some components are missing it contradicts this most basic definition of a key.
  4. In standard SQL a uniqueness constraint does not enforce uniqueness if any column permits nulls.
  5. SQL DBMSs also permit nulls in other constraints (except PRIMARY KEY): FOREIGN KEY, CHECK, ASSERTION. Nulls never violate the constraint so the null acts as a kind of "get out clause" for data integrity. Hypothetically, if nulls were permitted in keys then that would seem to imply a stricter form of referential integrity that didn't permit nulls in foreign keys unless there was a corresponding null key in the parent table. In that case every nullable foreign key would have to reference a nullable key and could not reference a non-nullable one.
  6. There are no nulls in reality, in everyday mathematics or logic; they are just a programming feature in SQL and are not particularly "mathematical" or even consistent in the way they work. Don't look for more reasons: nulls work the way they do just because the designers of SQL say so.
10
  • 1
    Saying null is not a value (whatever that means) or that it is a marker (whatever that means) just promotes confusion & imprecision & is an impediment to clear thinking. Null is a value treated in a certain way by SQL syntax & operators. In "everyday mathematics or logic" anything can be a value, and null is a structureless everyday boring one like 7.
    – philipxy
    Jun 8 '20 at 7:19
  • 1
    In standard SQL a uniqueness constraint does not enforce uniqueness if any column permits nulls. - see the thread following the question - apparently, the standard does not say this - multiple nulls are allowed for UNIQUE constraints! The thing everybody agrees on is that a NULL cannot be part of a PRIMARY KEY!
    – Vérace
    Jun 8 '20 at 10:35
  • @Vérace "multiple nulls are allowed" is correct. That's what I mean when I say that uniqueness is not enforced. It's possible to have duplicate rows if you allow nulls so the rows won't necessarily be unique in the case of a nullable UNIQUE constraint.
    – nvogel
    Jun 8 '20 at 17:33
  • @philipxy, Don't all values have the property of being equal to themselves? x=x is true if x is 7 but not if x is a null. Don't all values have the property of being not equal to other values? x='x is false if x is 7 but not if x is a null.
    – nvogel
    Jun 8 '20 at 17:39
  • 1
    SQL = is not equals on scalars. It's like equals but it treats null differently. SQL is not distinct from is equals on scalars.
    – philipxy
    Jun 8 '20 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.