There is a field that is the primary key. This field must not contain "zero" values.

Is there a need for NOT NULL , or is it enough just PRIMARY KEY ?

  • one
    write where? when creating a table? need to. example id INT NOT NULL AUTO_INCREMENT PRIMARY KEY - Alexey Shimansky
  • @ Alexey Shimansky, a new question for you. You say that it is necessary, others that are not necessary. I read information on the Internet. It says that in general, the PK field already has a constrait and NOT NULL PRIMARY KEY ** - "oil oil". In other sources it is said that for some cases it is necessary. So when should I write ** NOT NULL and PRIMARY KEY ? - up-and-coming
  • В интернете почитал информацию ...... В инных источниках сказанно, is better - write these same sources. I personally did not understand what you wrote. Perhaps something taken out of context. ....................... the PK field is already NOT NULL if in any editor to create a table: there when indicating that the PK column is ticked automatically and that she is NOT NULL. There is such - Alexey Shimansky
  • @ Alexey Shimansky, and if creating a table is console, then write NOT NULL separately? - up-and-coming
  • one
    what does it mean separately? what difference console or not ..... you write either CREATE TABLE MyTable ( Id int NOT NULL PRIMARY KEY,....... or CREATE TABLE MyTable ( Id int NOT NULL, ........еще столбцы, PRIMARY KEY (Id) ) ......... that is, the column that you deem will be the primary key - write as not null ............... w3schools. com / sql / sql_primarykey.asp → A primary key column cannot contain NULL values - Alex Shimansky

1 answer 1

Unique fields have one fun feature. Uniqueness is determined by equality. But NULL not equal to NULL ! Therefore, NULL values ​​can be added to a column with uniqueness checking in any number of rows!

And the primary key must uniquely identify each row by the equality of the value of this key . Therefore, the NULL value for the primary key does not make sense . When comparing equality, it simply never matches any string.

This applies to all SQL-compatible databases.
But I will quote the definition of the primary key from dev.mysql.com :

It’s possible to identify each row in a table. It doesn’t contain any NULL values.

A set of columns and an index on this set of columns that can uniquely identify each row in the table. This is a unique index that does not contain NULL values.

So yes, PRIMARY KEY requires its columns to be NOT NULL .

And depending on the syntax used to define the primary key, the NOT NULL indication may be mandatory or optional. In MySQL (apparently) always always.

The syntax for determining the primary key, I met three types:

  • Right when determining the type of a column, adding a PRIMARY KEY to its type:

     CREATE TABLE things ( id integer PRIMARY KEY )
    • Specifying NOT NULL may be optional. But in MySQL, judging by the examples (the documentation did not really help), it is necessary . In PostgreSQL, there is no: it is assumed that the PRIMARY KEY in the column type includes NOT NULL .

  • When defining a primary key as part of a table definition (but not its: columns)

     CREATE TABLE things ( id integer NOT NULL, PRIMARY KEY(id) )
    • NOT NULL obligatory, otherwise the reference to the column in PRIMARY KEY will conflict with the already declared.

  • When determining the primary key on an already created table separately.

    • NOT NULL mandatory since the table is already defined.
  • one
    and what exactly does the phrase "but not vice versa" mean? - aleksandr barakin
  • one
    Perhaps the question and stupid, after your explanation, but I ask him. So in case my id INT is PRIMARY KEY , I should write NOT NULL ? In some sources they write, like you, that PRIMARY KEY already has constraint , while others say - NOT NULL + PRIMARY KEY , because this is necessary in some situations. What kind of situations are these and, frankly, I don’t understand, if PRIMARY KEY is determined to be NOT NULL by default, almost in all examples it is explicitly defined - it means that if you define this limitation explicitly, can certain problems arise? - up-and-coming
  • and agree with alexander barakin, but what exactly is “but not vice versa”? not null ??????? - up-and-coming
  • Completed the answer. “But not vice versa” remains from the old edition, where it seemed to me unobvious that specifying NOT NULL does not automatically enlist the column in the primary key, which would be at least strange. - D-side
  • @ D-side, thank you so much) - up-and-coming