CODEWITHSUNDEEP
databasesqlitecreate-tabletablecolumndbtable
gruszczy
gruszczy

Reputation: 42228

How to create a table with no columns in SQLite?

I want to create table with no columns in sqlite3. It is possible in postgres database, but not in a sqlite3 one. Is there any way to achieve this, or is it simply not supported (maybe not in sql standard?) I have checked sqlite3 CREATE TABLE grammar and it seems, that there must be at least one column, but maybe I have missed something?

Upvotes: 21

Views: 11778

Answers (4)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You cannot create a table with no columns in SQLite but you can easily create a table with one column which is empty string with BLOB type as shown below. *If specifying no type, the type is BLOB type according to the doc:

CREATE TABLE person ("");

Or:

CREATE TABLE person ([]);

Or:

CREATE TABLE person (``);

And, you can add a row if you want as shown below:

INSERT INTO person ("") values ('John');

Or:

INSERT INTO person ([]) values ('John');

Or:

INSERT INTO person (``) values ('John');

Or:

INSERT INTO person values ('John');

Upvotes: -1

abdullah
abdullah

Reputation: 61

you can create table with only id column instead of creating empty table:

def create_table(DATABESE_NAME):
    conn = sqlite3.connect(DATABESE_NAME)
    c = conn.cursor()
    c.execute(''' CREATE TABLE IF NOT EXISTS rate_table(
    id INTEGER PRIMARY KEY AUTOINCREMENT) ''')
    conn.commit()
    conn.close()

Upvotes: 1

Allen
Allen

Reputation: 325

I had this same question because I wanted a table with only the rowid field. While you may not be able to create a table without columns, you can make a table with only a rowid field as the primary key using the following code:

CREATE TABLE tablename (rowid INTEGER PRIMARY KEY) WITHOUT ROWID;

Upvotes: 4

dan04
dan04

Reputation: 91227

Zero-column tables aren't supported in SQLite. Or in the SQL standard either.

Upvotes: 21

Related Questions