ALTER TABLE statement in SQL

ALTER TABLE is the SQL statement to add, remove, or modify columns in a table, it is also used to add or remove constraints on it.

Next, we will see with simple examples on the customers table used as an example in the CREATE TABLE section, how to use the ALTER TABLE statement to modify our databases.

ADD statement

ADD allows us to add new columns and constraints to a table within an ALTER TABLE statement.

The statement has the following syntax:

ALTER TABLE tableName ADD newColumn dataType [CONSTRAINT]

ADD columns

ADD newColumn allows adding new columns specifying the data type of the new field or fields. When we define the new column we can also specify the aplicable properties and constraints.

For our example you can write the following code to add a new column called driversLicense to our Customers table that will not admit null values:

ALTER TABLE Customers
ADD driversLicense VARCHAR (20) NOT NULL;

In case we wanted to add more than one column to a table, we can do it for a single statement. For our example, you can write the following code block to add the new columns driversLicense, nationality and registrationDate:

ALTER TABLE Customers
ADD driversLicense VARCHAR (20) NOT NULL,
    nationality VARCHAR (50),
    registrationDate DATE;

ADD CONSTRAINTS

ADD CONSTRAINT newConstraint allows us to add new constraints (such as Primary Key or indexes) to the columns of a existing table.

The statement uses the following syntax:

ALTER TABLE tableName CONSTRAINT newConstraint

The following example adds an UNIQUE constraint to the driversLicense column.

ALTER TABLE Customers
ADD CONSTRAINT uq_customers_driversLicense
    UNIQUE (driversLicense);

It is also possible to add a column and a constraint in the same statement as we can see in the example below:

ALTER TABLE Customers
ADD driversLicense VARCHAR (20),
CONSTRAINT uq_customers_driversLicense
    UNIQUE (driversLicense);

DROP statement

DROP within an ALTER TABLE statement allows deleting columns and constraints for a table.

The statement has the following syntax:

ALTER TABLE tableName DROP [COLUMN columnName][CONSTRAINT constraintName]

DROP COLUMNS

DROP COLUMN newColumn deletes the specified columns from a table.

The statement has the following syntax:

ALTER TABLE tableName DROP COLUMN columnName

In the following example we will delete the address column from the Customer table.

ALTER TABLE Customers
DROP COLUMN [address];

We can also delete more than one column in a single statement. In the following example we delete the nationality and registrationDate from the Customer table.

ALTER TABLE Customers
DROP COLUMN nationality, 
            registrationDate;

DROP CONSTRAINTS

DROP CONSTRAINT constraintName allows deleting constraints from the columns of a table.

The statement has the following syntax:

ALTER TABLE tableName DROP CONSTRAINT constraintName

The next example deletes the UNIQUE constraint uq_customers_driversLicense applied to the driversLicense columns.

ALTER TABLE Customers
DROP CONSTRAINT uq_customers_driversLicense;

ALTER statement

ALTER COLUMN within an ALTER TABLE statement allows modifying the columns of a table.

ALTER COLUMN

The statement has the following syntax:

ALTER TABLE tableName ALTER COLUMN columnName dataType

The following example modifies the INT data type from the bankAccount column, changing it to VARCHAR(30):

ALTER TABLE Customers
ALTER COLUMN bankAccount VARCHAR (30);