Sqlite: adding COMMENT ON descriptions to tables and columns?

Question

In MySQL Workbench you can add COMMENTs to tables and columns in a MySQL database.

Does Sqlite support adding comments to tables and columns?

score 62 · Accepted Answer · answered Sep 15 '11 at 05:39

I don't think it does. The "SQL As Understood By SQLite" page makes no mention of table or column comments nor does the CREATE TABLE or ALTER TABLE documentation.

Also, the Unsupported SQL wiki page has this:

2009-08-04: Table and column comments - I have scoured the doco and can't find anything about applying comments to tables or their columns.

Yes, that's a wiki page from 2009 but that note is supported by the rest of the documentation.

However, SQLite does preserve SQL comments that you put in your DDL. If you feed this to the sqlite3 CLI tool:

CREATE TABLE User
        -- A table comment
(
        uid INTEGER,    -- A field comment
        flags INTEGER   -- Another field comment
);

Then you get exactly that back from a .schema command:

sqlite> .schema
CREATE TABLE User
        -- A table comment
(
        uid INTEGER,    -- A field comment
        flags INTEGER   -- Another field comment
);

So you should be able to fake it if you can control the DDL used to create your tables.

As well as using `.schema`, you can obtain the same result as an SQL statement using: `SELECT name, sql FROM sqlite_master WHERE type='table' AND name='{$table}' ORDER BY name;` (where $table is the name of the table). See: http://stackoverflow.com/a/6617764/180733 — fooquency, Feb 12 '14 at 23:27
This is great, though unfortunately the comments do not appear in SQLiteStudio's DDL view. — Timmmm, Sep 21 '20 at 11:18

score 2 · Answer 2 · answered Oct 11 '16 at 19:16

When creating a table using sqlite (I'm using sqlite3 in python), the COMMENT section is not supported.

This fails (works in full MySql syntax):

CREATE TABLE `Info` (
  `Test` VARCHAR(512) NOT NULL COMMENT 'Column info here'
);

This works (no COMMENT in the column declaration):

CREATE TABLE `Info` (
  `Test` VARCHAR(512) NOT NULL
);

Suragch · Answer 3 · 2020-09-14T06:21:53.800

1

(This isn't what the original poster was asking, but this is what I was looking for when I first found this question based on the keywords in the title.)

How to make comments in SQLite

There are two ways to make comments in SQLite code:

Hyphens

-- this is my comment
SELECT * FROM employees;

C-style

/* this is my comment */ 
SELECT * FROM employees;

edited Sep 14 '20 at 06:21

answered Feb 26 '18 at 11:44

Suragch

484,302
314
1,365
1,393

score 0 · Answer 4 · edited Jul 11 '23 at 09:17

I appreciate that this is an old post but for what it's worth, you can add comments when creating a table in SQLITE3, in Python and in Java. Probably works for other languages as well.

You need to add new lines to your sql string as you would if you were typing in the command at the SQLITE3 prompt -

sql_str = 'CREATE TABLE properties (\nproperty TEXT NOT NULL, -- A property\nvalue TEXT -- The value of the property\n);'

When executed the table is created like so:

sqlite> .schema

CREATE TABLE properties (
property TEXT NOT NULL, -- A property
value TEXT -- The value of the property
);

I suspect that this works because the connector is actually echoing in the commands via the command prompt, rather than some sort of API.

Sqlite: adding COMMENT ON descriptions to tables and columns?

4 Answers4

How to make comments in SQLite

Linked