How to Add a Column in SQL

Problem

You want to add a new column to an existing table.

Example

We would like to add the column color of the datatype varchar to the table called jeans.

Solution

ALTER TABLE jeans
ADD color varchar(100) NOT NULL;

Discussion

SQL provides the statement ALTER TABLE that allows you to change the structure of a table. It can be used to modify the table by adding a new column. Place the ALTER TABLE keyword followed by the name of the table you want to change. The next is the keyword ADD, after which the name of the new column is specified. It is then followed by the definition of the column: the datatype and any additional constraints. Note that the definition of the column after the ADD keyword is the same as when you define a column for a new table with the CREATE TABLE statement.

In the example above, we modified the structure of the table jeans. The name of the table, jeans follows the ALTER TABLE. We specify the column to be named, color, after the ADD keyword. At the end of the statement, we specify varchar(100) as the datatype for the values that will be stored in the column color, and the constraint NOT NULL because we don’t want to allow empty values in this column. However, if the table has records, first add the new column allowing NULL, update the data, then in the final step change the definition of the column to NOT NULL.