SQLite Forum

CREATE TABLE allowing empty column name
Login

CREATE TABLE allowing empty column name

(1) By Brian Minton (bjmgeek) on 2020-04-23 13:19:28 [source]

https://www.sqlite.org/syntax/column-def.html shows column-name as required.  However, the following does not give an error.:

$ sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo(integer primary key);
sqlite> insert into foo values (100);
sqlite> select * from foo;
100
sqlite> 

Is this an undocumented feature, or a bug?  Or is this normal SQL syntax that I'm just misunderstanding?  I see that it's not just the rowid column:

sqlite> select rowid from foo;
1

I was trying create a table with only one column, rowid.  This is the intended behavior:

sqlite> drop table foo;
sqlite> create table foo(rowid integer primary key);
sqlite> insert into foo values (100);
sqlite> select rowid from foo;
100
sqlite> select * from foo;
100

Update: After further testing I discovered that the first example made a column called `integer`.

(2) By Richard Hipp (drh) on 2020-04-23 14:02:19 in reply to 1 [link] [source]

This is a documented feature. The documentation is at https://www.sqlite.org/quirks.html#keywords_can_often_be_used_as_identifiers. If you will run:

    PRAGMA table_info=foo;

You'll find that you have created a table with a single column named "integer" with no datatype specified and which is the PRIMARY KEY. The table is the same as:

    CREATE TABLE foo(bar PRIMARY KEY);

Except that in your table the column is named "integer" instead of "bar".

SQLite has long had the ability to use keywords as identifiers. This is because SQL has many keywords that can potentially collide with identifier names, and as we add new keywords when new features are added, the new keywords do not break legacy schemas. The ability to use keywords as identifiers promotes extreme backwards compatibility.