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

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]

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.