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

user_9500955 0
Viewed 900

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?

sqlite mysql sql
3 Answers

Thanks to @astentx advice, I check 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.

user_95009550

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

user_79177850
user_36563870
Related