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.