SQL Alter Table

The SQL Server Alter Table Statement is used to alter the SQL Server Tables. This 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.

In this article, we will show you, How to use the SQL Alter Table statement to alter the table definition with a live example. For this, We are going to use the below-shown Customer table inside the [SQL Tutorial] Database.

SQL Alter Table examples

Following are some of the examples to add, modify and delete columns and constraints

SQL Alter Table Add New Column

In order to add a new column to the existing SQL 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 snippet, you can observe that the Education Column belongs to varchar data type, the data length is 50 and it will allow NULL Values

OUTPUT

SQL Alter Table Delete Column

In order 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

OUTPUT

SQL Alter Table Change Column Data Type

In order to change the existing columns Data Type from the SQL table, Please 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

In this example, 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)

If you want to alter the NULL functionality to restrict the NULL or allow NULLS. Please use the following code

-- SQL Server Alter table change Column data type
ALTER TABLE [Customer]
ALTER COLUMN Education NVARCHAR(75) NOT NULL

OUTPUT

From the above screenshot, you can observe that we successfully changed the data type, length and NULL handling of existing column present in SQL Table

SQL Alter Table Add primary key

In order to add Primary keys and a foreign key to existing columns, Please use the following syntax

-- SQL Alter Table Add primary key
ALTER TABLE [Customer]
ADD CONSTRAINT [Constraint_Name] PRIMARY KEY ([Column_Name])

In this example, we are going to 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] )

OUTPUT

SQL Alter Table Drop Constraint

In order to delete or Drop primary keys and foreign key on existing columns, Please use the following syntax

-- SQL Server ALTER Table Drop Constraint
ALTER TABLE [Customer]
DROP CONSTRAINT [Constraint_Name]

In this example, we are going to delete the previously created Primary Key constraint on the Customer Key Column

-- SQL Server ALTER Table Drop Constraint
ALTER TABLE [Customer]
DROP CONSTRAINT PrimaryKey

Thank You for Visiting Our Blog