ADD vs. ADD COLUMN in SQLite?

user_4967485 0
Viewed 1859

I am trying to add a new column to a pre-existing table on a database upgrade method. I've seen two ways:

ALTER TABLE foo ADD new_column INTEGER DEFAULT 0

and

ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0

What's the difference?

syntax sqlite sql
2 Answers

COLUMN is not required by the SQL spec, but it is preferred. Tables could have other things you can ADD through proprietary extensions of the spec. The spec does not tell you what you can NOT do, so it's better to be explicit.

The actual BNF grammar in the SQL-92 spec is,

<alter table statement> ::=
    ALTER TABLE <table name> <alter table action>

<alter table action> ::=
    <add column definition>
    | <alter column definition>
    | <drop column definition>
    | <add table constraint definition>
    | <drop table constraint definition>

<add column definition> ::=
    ADD [ COLUMN ] <column definition>

The stuff in [ ] is optional.

The same thing is true for other similar commands which need a column definition, like ALTER COLUMN and DROP COLUMN.

<alter column definition> ::=
    ALTER [ COLUMN ] <column name> <alter column action>

<drop column definition> ::=
    DROP [ COLUMN ] <column name> <drop behavior>

copied from another answer, on a question which this was marked as a duplicate

user_1244860
Related