In SQL, you can use the ALTER TABLE statement to add one or more columns to an existing table.

To do this, use the ADD clause, followed by the column definition. Separate multiple columns with a comma.

Only specify ADD once (i.e. there’s no need to specify it for each column).

Add One Column

Here’s a basic example where I add one column to an existing table.

ALTER TABLE Test ADD 
  column_2 varchar(50) NOT NULL;

In this case I added a column called column_2 to the table called Test.

This could easily have been done on a single line. I only use two lines here for easier readability.

Just to be clear, here it is again, but this time I create the table before altering it.

CREATE TABLE Test 
  (
      column_1 int IDENTITY NOT NULL PRIMARY KEY
  );
ALTER TABLE Test ADD 
  column_2 varchar(50) NOT NULL;

Note that the examples on this page were done using SQL Server. Depending on your DBMS, you may need to change some details of the column definitions.

For example IDENTITY is SQL Server’s version of what is sometimes called AUTO_INCREMENT in other DBMSs (such as MySQL). If you use SQLite, then here’s how to create an auto-incrementing column in SQLite.

Add Multiple Columns

Here’s how to add two or more columns.

ALTER TABLE Test ADD 
  column_3 int NULL, 
  column_4 int NULL;

As mentioned, you simply separate each column with a comma.

Add Constraints

You can also add constraints to any columns that you add to an existing table. Here’s an example.

ALTER TABLE Test ADD 
  column_5 int NOT NULL
  CONSTRAINT u_column_5 UNIQUE;

This example adds a column called column_5 with a UNIQUE constraint called u_column_5.

Obviously, the actual code you use will depend on the column name and constraint that you are adding, as well as its type. For example, you could be adding a primary key constraint, a foreign key constraint, a CHECK constraint, etc.

Also, this isn’t the only way you can add constraints to a column. If the column already exists, you can use ADD CONSTRAINT to add a constraint against the existing column.

Adding the constraint when you add the column simply saves you from having to add it later.