SQLite - named constraint syntax

Question

I wonder why the following syntax is working:

CREATE TABLE tab1(id INT,
                  PRIMARY KEY (id) CONSTRAINT PK_tab1
                  -- here the CONSTRAINT is after PRIMARY KEY def
);

CREATE TABLE tab3(id INT,  
                  CHECK(id > 10) CONSTRAINT CHK_tab3_id 
);
                  

Normally I would expect:

CREATE TABLE tab2(id INT,
                  CONSTRAINT PK_tab2 PRIMARY KEY (id) 
); 

db<>fiddle SQLite demo

Based on documentation it seems to be invalid syntax:

CREATE TABLE table_constraint

---

For PostgreSQL/Oracle/MySQL/SQL Server above syntax returns error: db<>fiddle demo PostgreSQL

Is this some kind of compatibility mode with DB2/Informix?

Answer 1

What you're missing is in the column-def diagram.

Notice the loop in the column-constraint section; a single column can have multiple constraints. So your first example has the primary key, and then a second named constraint that doesn't actually have any actual rules applied to it - which is the unusual bit, as the diagrams suggest that shouldn't be allowed. However, the sqlite3 parser is very, very, very forgiving when it comes to column definitions and allows things the official syntax diagrams suggest it shouldn't.