Why can I add null value to primary key in SQLite?

Question

I have this code, you can try on db-fiddle:

CREATE TABLE EMPLOYEE
( ID        INT,
  Name      VARCHAR(15) NOT NULL,
  Age       INT,
  Dnumber   INT,
  Level     INT,
  PRIMARY KEY (ID)
);

INSERT INTO EMPLOYEE (ID, Name, Age, Dnumber, Level) VALUES(1,'Tom', 21, 2, 5);
INSERT INTO EMPLOYEE (ID, Name, Age, Dnumber, Level) VALUES(2,'Lucky', 22, 2, 3);
INSERT INTO EMPLOYEE (ID, Name, Age, Dnumber, Level) VALUES(NULL,'Blue', 22, 2, 3);

If I use SQLite, I can add the NULL value but it cannot if I use MySQL. I will get this error:

Schema Error: Error: ER_BAD_NULL_ERROR: Column 'ID' cannot be null

What is the reason why I can add it to SQLite?

Kuth

I can't speak for SQLite but primary key column aren't nullable in any database engine I work with. – Barbarism

@ÁlvaroGonzález Yes, that right. I just don't know why SQLite allows it to be null. – Kuth

@Kuth What about checking in the documentation? Section 3.5 – Tolerable

You should use INTEGER, not INT, for a primary key affinity in sqlite - see sqlite.org/lang_createtable.html#rowid – Embroideress

To be precise INTEGER PRIMARY KEY, as documentation says in link for section 3.5. INT is just shorthand for INTEGER, but that phrase has another meaning syntactically and should be read as one element. – Tolerable

Answer 1

Thanks to @astentx for the advice. I checked the documentation and it said that:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

Kuth

I can't believe they allowed null values to lurk around in primary keys, using the legacy excuse. Not conforming to the standard simply creates more confusion for everyone. – Lara

@Alin Gabriel Arhip agreed this seems outrageous. Isn't that was versioning is for? – Etalon

Omg this is bad, LOL. – Straightjacket

Actually in sqlite PRIMARY KEY is the same as UNIQUE (except there is only one pk). You can think the rowid as the actual pk. – Cetacean

Answer 2

In SQLite basically, you can add NULL to PRIMARY KEY columns but you cannot add NULL to INTEGER PRIMARY KEY and INTEGER PRIMARY KEY AUTOINCREMENT columns even if they don't have NOT NULL constraint. My answer explains it more.

Stanwinn

Answer 3

PRIMARY KEY (ID) isn't allowed to store NULL for MySQL, if i remember correctly. So your ID column can't store NULL values.

Karaganda

Please provide an answer for the question "What is the reason why I can add it to SQLite?" - the OP already stated that NULL is not allowed in MySQL – Cartwright

Answer 4

ID field is the primary key(a primary index exists on the field), that is why the field cannot accept the null values. It should have not null unique values.

Official Doc:

https://dev.mysql.com/doc/refman/8.0/en/constraint-primary-key.html

Montfort

Please provide an answer for the question "What is the reason why I can add it to SQLite?" - the OP already stated that NULL is not allowed in MySQL – Cartwright