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