ALTER TABLE in SQL

While working in SQL, what will you do when you need to add a constraint to a table or when you need to add or remove a column from the table?

Luckily, ALTER TABLE command of SQL provides such utility.

ALTER TABLE can be used for the following purposes:

• Adding a new column

• Dropping an already existing column

• Modifying the datatype of an already existing column

• Adding a constraint

• Dropping a constraint

Kindly note, this tutorial is created for syntax of Microsoft SQL server. Our dataset name is considered as patient_info.

Adding a new column

Task: Add a new column type_of_diagnosis in the table patient_info

ALTER TABLE patient_info
ADD type_of_diagnosis varchar(100);

Removing an already existing column

Task: Remove column admit_date from the table patient_info

ALTER TABLE patient_info
DROP COLUMN admite_date;

Changing datatype of an already existing column

Task: Change data type of department to varchar(128) in the table patient_info

ALTER TABLE patient_info
ALTER COLUMN department varchar(128);

Adding a constraint

Adding a UNIQUE constraint

Task: Add unique constraint to columns patient_id, department in the table patient_info.

ALTER TABLE patient_info
ADD CONSTRAINT my_constraint UNIQUE(patient_id,department);

Note: Since patient_id, department are written together it means that patient_id can have repeated values and department column can have repetitions but patient_id and department combination will have unique values.

Adding a NOT NULL constraint

Task: Add a NOT NULL constraint to column patient_id in the table patient_info.ALTER TABLE patient_info

ALTER COLUMN patient_id int NOT NULL;

Adding a PRIMARY KEY constraint

Primary Key Tutorial PRIMARY KEY constraints have two conditions: The columns on which the constraint is being applied should have NOT NULL constraint and should have UNIQUE values.

By default while creating a dataset every column has NULL constraint. Thus before applying the PRIMARY KEY constraint using ALTER TABLE, firstly we need to apply the NOT NULL constraint first and then the PRIMARY KEY constraint.

Task: Add a PRIMARY KEY constraint to columns patient_id and department in the table patient_info.

ALTER TABLE patient_info
ALTER COLUMN patient_id int NOT NULL;

ALTER TABLE patient_info
ALTER COLUMN department varchar(128) NOT NULL;

ALTER TABLE patient_info
ADD PRIMARY KEY(patient_id,department);

Note: If you already have NULL values or duplicate values in the dataset then you are likely to encounter errors in applying UNIQUE or NOT NULL constraints. How to deal with this?

Firstly, remove the NULL values by either deleting the rows or replacing them using UPDATE statement and tackle the duplicate rows.

Dropping a constraint using ALTER TABLE

Task: Drop already existing constraint my_constraint from the table patient_info.

ALTER TABLE patient_info
DROP CONSTRAINT my_constraint;