add-column.md

title	summary	toc
ADD COLUMN	Use the ADD COLUMN statement to add columns to tables.	true

The ADD COLUMN statement is part of ALTER TABLE and adds columns to tables.

Synopsis

{% include {{ page.version.version }}/sql/diagrams/add_column.html %}

Required Privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter	Description
table_name	The name of the table to which you want to add the column.
column_name	The name of the column you want to add. The column name must follow these identifier rules and must be unique within the table but can have the same name as indexes or constraints.
typename	The data type of the new column.
col_qualification	An optional list of column definitions, which may include column-level constraints, collation, or column family assignments. Note that it is not possible to add a column with the Foreign Key constraint. As a workaround, you can add the column without the constraint, then use CREATE INDEX to index the column, and then use ADD CONSTRAINT to add the Foreign Key constraint to the column.

Viewing Schema Changes

{% include {{ page.version.version }}/misc/schema-change-view-job.md %}

Examples

Add a Single Column

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN names STRING;

{% include copy-clipboard.html %}

> SHOW COLUMNS FROM accounts;

+-----------+-------------------+-------+---------+-----------+
|   Field   |       Type        | Null  | Default |  Indices  |
+-----------+-------------------+-------+---------+-----------+
| id        | INT               | false | NULL    | {primary} |
| balance   | DECIMAL           | true  | NULL    | {}        |
| names     | STRING            | true  | NULL    | {}        |
+-----------+-------------------+-------+---------+-----------+

Add Multiple Columns

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN location STRING, ADD COLUMN amount DECIMAL;

{% include copy-clipboard.html %}

> SHOW COLUMNS FROM accounts;

+-----------+-------------------+-------+---------+-----------+
|   Field   |       Type        | Null  | Default |  Indices  |
+-----------+-------------------+-------+---------+-----------+
| id        | INT               | false | NULL    | {primary} |
| balance   | DECIMAL           | true  | NULL    | {}        |
| names     | STRING            | true  | NULL    | {}        |
| location  | STRING            | true  | NULL    | {}        |
| amount    | DECIMAL           | true  | NULL    | {}        |
+-----------+-------------------+-------+---------+-----------+

Add a Non-Null Column with a Default Value

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN interest DECIMAL NOT NULL DEFAULT (DECIMAL '1.3');

{% include copy-clipboard.html %}

> SHOW COLUMNS FROM accounts;

+-----------+-------------------+-------+---------------------------+-----------+
|   Field   |       Type        | Null  |          Default          |  Indices  |
+-----------+-------------------+-------+---------------------------+-----------+
| id        | INT               | false | NULL                      | {primary} |
| balance   | DECIMAL           | true  | NULL                      | {}        |
| names     | STRING            | true  | NULL                      | {}        |
| location  | STRING            | true  | NULL                      | {}        |
| amount    | DECIMAL           | true  | NULL                      | {}        |
| interest  | DECIMAL           | false | ('1.3':::STRING::DECIMAL) | {}        |
+-----------+-------------------+-------+---------------------------+-----------+

Add a Non-Null Column with Unique Values

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN cust_number DECIMAL UNIQUE NOT NULL;

Add a Column with Collation

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN more_names STRING COLLATE en;

Add a Column and Assign it to a Column Family

Add a Column and Assign it to a New Column Family

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN location1 STRING CREATE FAMILY new_family;

Add a Column and Assign it to an Existing Column Family

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN location2 STRING FAMILY existing_family;

Add a Column and Create a New Column Family if Column Family Does Not Exist

{% include copy-clipboard.html %}

> ALTER TABLE accounts ADD COLUMN new_name STRING CREATE IF NOT EXISTS FAMILY f1;

See Also

• ALTER TABLE
• Column-level Constraints
• Collation
• Column Families