The SQL Server Alter Statement is used to alter the SQL Server Tables such as Adding New Columns, Modifying existing Columns, Deleting existing Column, Add or Removing Indexes, Dropping and adding Constrains 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 SQL table definition with 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
Adding New Column
In order to add new column to the existing SQL table, Please use the following syntax
|
1 2 |
ALTER TABLE [Table_Name] ADD [New_Column_Name] Data_Type (Length) NULL | NOT NULL |
In this example, we are going to add Education Column to the existing table customers
SQL CODE
|
1 2 3 |
ALTER TABLE [Customer] ADD Education VARCHAR (50) NULL |
From the above code snippet you can observe that, Education Column belongs to var char data type, data length is 50 and it will allow NULL Values
OUTPUT
Deleting Columns
In order to delete or drop the existing column from the SQL table, Please use the following syntax
|
1 2 |
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 CODE
|
1 2 3 |
ALTER TABLE [Customer] DROP COLUMN Profession |
OUTPUT
Changing Column Data Type
In order to change the existing columns Data Type from the SQL table, Please use the following syntax
|
1 2 |
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 Var char to Nvarchar and length from 50 to 75
SQL CODE
|
1 2 3 |
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
|
1 2 3 |
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
Adding Constraint or adding primary key
In order to add Primary keys and foreign key to existing columns, Please use the following syntax
|
1 2 |
ALTER TABLE [Customer] ADD CONSTRAINT [Constraint_Name] PRIMARY KEY ([Column_Name]) |
In this example, we are going to add Primary Key constraint to the Customer Key Column
SQL CODE
|
1 2 |
ALTER TABLE [Customer] ADD CONSTRAINT PrimaryKey PRIMARY KEY ( [CustomerKey] ) |
OUTPUT
Deleting or Drop Constraint
In order to delete or Drop primary keys and foreign key on existing columns, Please use the following syntax
|
1 2 |
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
|
1 2 3 |
ALTER TABLE [Customer] DROP CONSTRAINT PrimaryKey |
Thank You for Visiting Our Blog
Hi Suresh,
Your page is really amazing and easy to learn. One thing need to be add as "Next" tab so that we can easily look forward to the next page..
This time we need to go back for every next topic selection if this will add it will be good for learners.
Thanks..