Day 33 – SQL ALTER TABLE
SQL ALTER TABLE statement is used to modify the structure of an existing table in a database. The syntax for the ALTER TABLE statement is as follows:
ALTER TABLE table_name
ADD column_name datatype [NULL | NOT NULL] [DEFAULT default_value];
ALTER TABLE table_name
ALTER COLUMN column_name datatype [NULL | NOT NULL] [DEFAULT default_value];
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name [NULL | NOT NULL];
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column1, column2, ...);
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
In this syntax, “table_name” is the name of the table that you want to modify, and the different options following the ALTER TABLE statement allow you to add, modify, or remove columns, change column data types or constraints.
Here’s an example of how to add a new column named “email” of data type VARCHAR(100) to an existing table named “employees”:
ALTER TABLE employees
ADD email VARCHAR(100) NOT NULL;
When this statement is executed, SQL Server will add a new column named “email” of data type VARCHAR(100) to the “employees” table.
You can also modify existing columns by changing their data type or nullability, as shown in the following example:
ALTER TABLE employees
ALTER COLUMN email VARCHAR(200) NULL;
This statement modifies the “email” column of the “employees” table, changing its data type to VARCHAR(200) and allowing null values.
You can also add or remove constraints on the columns of an existing table, as shown in the following example:
ALTER TABLE employees
ADD CONSTRAINT pk_employee PRIMARY KEY (id);
ALTER TABLE employees
DROP CONSTRAINT pk_employee;
This statement first adds a primary key constraint named “pk_employee” on the “id” column of the “employees” table, and then removes it.