Problem with Composite Primary Key ?

[RuudSchmeitz]

I created table X with a composite primary key :
CREATE TABLE X (A TEXT, B TEXT, PRIMARY KEY (A, B) );

Then I tried to insert 4 rows into table X :
INSERT INTO X (A, B) VALUES ('test1', 'test2');
INSERT INTO X (A, B) VALUES ('test1', 'test2');
INSERT INTO X (A, B) VALUES ('test1', NULL);
INSERT INTO X (A, B) VALUES ('test1', NULL);

As expected , the first INSERT statement executed OK , and the second does not :
"Error while executing SQL query on database 'MyPrivData': UNIQUE constraint failed: X.A, X.B"

Again as expected , no problems with the third statement .
But surprisingly , the fourth statement executed OK as well , resulting in :

Aren't the last 2 rows identical ?
Is that consistent with PRIMARY KEY (A, B) ?

[pawelsalawa]

This is behavior defined by SQLite, not SQLiteStudio and it is expected.

[pawelsalawa]

From sqlite.org:

Each row in a table with a primary key must have a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows have identical primary key values, that is a constraint violation.

https://sqlite.org/lang_createtable.html#the_primary_key

[RuudSchmeitz]

Thanx for the link , Paweł .

And the next paragraph in the linked text , explains that this is not standard SQL behaviour :

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 allows NULLs in most PRIMARY KEY columns.

[pawelsalawa]

Indeed. As I said, it's defined by SQLite. Not SQL.