SQL ALTER Keyword

ALTER TABLE

The ALTER TABLE command is used to add, delete, or modify columns in a table. It can also be used to add and delete various constraints in a table.

SQL ALTER Syntax – Add Column

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name

ADD column_name datatype;

SQL ALTER Example – Add Column

Add a middle_name column to the Patients table:

ALTER TABLE Patients
ADD middle_name VARCHAR(50);

ALTER TABLE Patients

ADD middle_name VARCHAR(50);

SQL ALTER Syntax – Drop Column

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name

DROP COLUMN column_name;

SQL ALTER Example – Drop Column

Remove the middle_name column from the Patients table:

ALTER TABLE Patients
DROP COLUMN middle_name;

ALTER TABLE Patients

DROP COLUMN middle_name;

ALTER COLUMN

The ALTER COLUMN command is used to change the data type of a column in a table.

SQL ALTER Syntax – Alter Column Data Type

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;

ALTER TABLE table_name

ALTER COLUMN column_name TYPE new_datatype;

Note: Note: SQLite (our playground) does not support changing a column’s data type directly using ALTER COLUMN. Instead, you must:

1. Rename the new table.

2. Create a new table with the desired schema.

3. Copy the data from the old table.

4. Drop the old table.

SQL ALTER Example – Alter Column Data Type

Change the data type of the contact_number column in the Patients table to a longer VARCHAR:

ALTER TABLE Patients
ALTER COLUMN contact_number TYPE VARCHAR(25);

ALTER TABLE Patients

ALTER COLUMN contact_number TYPE VARCHAR(25);

Note: This will not work in our playground.

SQL ALTER Labs