The Alter Table statement is used to modify Tables. The SQL Server Alter Table includes Add New Columns, Modifying existing Columns, Deleting existing Columns, Adding or Removing Indexes, and Dropping and adding Constraints like Primary Keys to the existing table.
Table of contents
SQL Alter Table examples
The following are some examples of Alter Table to add, modify and delete columns and constraints. Let us see how to use this statement to change the table definition with a live example. For this Query, We use the Customer table.
SQL Alter Table Add New Column
To add a new column to the existing table, please use the following syntax.
ALTER TABLE [Table_Name] ADD [New_Column_Name] Data_Type (Length) NULL | NOT NULL
We are going to add the Education Column to the existing table customers.
ALTER TABLE [Customer] ADD Education VARCHAR (50) NULL
From the above code Server snippet, the Education Column belongs to the varchar data type, the data length is 50, and it will allow NULL Values.
Alter Table Delete Column
To delete or drop the existing column from the table, Please use the following syntax.
ALTER TABLE [Table_Name] DROP COLUMN [Column_Name]
We will delete the existing column called Profession from the customer table.
ALTER TABLE [Customer] DROP COLUMN Profession
Use SQL Alter Table to Change Column Data Type
To Alter or modify the existing columns Data Type in a table, use the following syntax.
-- change Column data type ALTER TABLE [Table_Name] ALTER COLUMN [Column_Name] Data_Type NULL |NOT NULL
Here, we change the Education column data type from Varchar to Nvarchar and length from 50 to 75
-- change data type ALTER TABLE [Customer] ALTER COLUMN Education NVARCHAR(75)
To change the NULL functionality to restrict the NULL or allow NULLS, use the following code.
-- change Column data type ALTER TABLE [Customer] ALTER COLUMN Education NVARCHAR(75) NOT NULL
We successfully changed the data type, length, and NULL handling of the existing column present in the SQL Table.
SQL Alter Table Add primary key
To add a Primary key and a foreign key to existing columns, please use the following SQL Server alter table add constraint syntax.
-- Add primary key ALTER TABLE [Customer] ADD CONSTRAINT [Constraint_Name] PRIMARY KEY ([Column_Name])
Add a Primary Key constraint to the Customer Key Column
-- Add primary key ALTER TABLE [Customer] ADD CONSTRAINT PrimaryKey PRIMARY KEY ( [CustomerKey] )
Alter Table Drop Constraint
To delete or Drop primary keys and foreign keys on existing columns, use the Alter Table Drop constraint syntax.
-- Drop Constraint ALTER TABLE [Customer] DROP CONSTRAINT [Constraint_Name]
Delete the previously created Primary Key constraint on the Customer Key Column.
-- Drop Constraint ALTER TABLE [Customer] DROP CONSTRAINT PrimaryKey