Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlite3: creating table with no columns

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?

like image 957
gruszczy Avatar asked Dec 30 '10 23:12

gruszczy


People also ask

Can I create table without any column?

A table is a collection of columns and rows. You need at least one column. in postgresql you can indeed create tables without columns.

How do you delete a column from a table in sqlite3?

DB Browser for SQLite allows you to add or drop columns. In the main view, tab Database Structure , click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.

Can we create empty table?

If you want the table to be empty use the WHERE 1=0 or TOP (0) on both sides of the UNION ALL. If you want a copy of the table with data then just put the WHERE 1=0 or TOP (0) on one side.


2 Answers

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

like image 83
dan04 Avatar answered Sep 30 '22 17:09

dan04


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;
like image 37
Allen Avatar answered Sep 30 '22 19:09

Allen