Alter Table is a DDL (Data Definition Language) statement which is used to add, drop, or alter existing columns. With this statement, you can also rename a table or column and make constraints. Using a table as an example, this tutorial demonstrates alter table statements with syntax.
You can download the examples used in this article and try them out on your local machine here. You can verify the changes to table structure using sp_help ‘Employee’.
First, we begin by creating a table.
Create Table Employee
(
EmployeeId INT,
EmployeeName VARCHAR(25),
Salary INT,
ContactNo VARCHAR(10),
DeptId INT,
Active BIT,
);
Create Table Department
(
DeptId INT PRIMARY KEY,
DeptName VARCHAR(50)
);
SQL Server – Add a Column to an Existing Table
Syntax
ALTER TABLE [TableName]
ADD [ColumnName] [DataType] [Null OR Not Null]
Example
ALTER TABLE Employee
ADD Address VARCHAR(255) NOT NULL
SQL Server – Drop an Existing Column from a Table
Syntax
ALTER TABLE [TableName]
DROP COLUMN [ColumnName]
Example
ALTER TABLE Employee
DROP COLUMN ADDRESS
SQL Server – Change data type of an Existing column
Syntax
ALTER TABLE [Tablename]
ALTER COLUMN [ColumnName] [DataType]
Example
ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(18,2)
SQL Server – Change the Size of an Existing Column
Syntax
ALTER TABLE [TableName]
ALTER COLUMN [Columnname] [DataType]
Example
ALTER TABLE Employee
ALTER COLUMN EmployeeName VARCHAR(50)
SQL Server – Add A Primary Key Constraint to an Existing Table
Syntax
ALTER TABLE [TableName]
ADD CONSTRAINT [Constraint Name] PRIMARY KEY (ColumnName)
Example
ALTER TABLE Employee
ADD CONSTRAINT PK_Employee_EmployeeId PRIMARY KEY (EmployeeId)
SQL Server – Drop the Existing Primary Key Constraint on an Existing Table
Syntax
ALTER TABLE [TableName]
DROP CONSTRAINT [ConstraintName]
Example
ALTER TABLE Employee
DROP CONSTRAINT PK_Employee_EmployeeId
SQL Server – Add a Foreign Key Constraint
Syntax
ALTER TABLE [TableName]
ADD CONSTRAINT [Constraintname] FOREIGN KEY (ColumnName) REFERENCES [TableName] (ColumnName)
Example
ALTER TABLE Employee
ADD CONSTRAINT Fk_Employee_DeptId FOREIGN KEY (EmployeeId) REFERENCES Department(DeptId)
SQL Server – Drop a Foreign Key Constraint
Syntax
ALTER TABLE [TableName]
DROP CONSTRAINT [Foreign_Key_Constraint_Name]
Example
ALTER TABLE Employee
DROP CONSTRAINT Fk_Employee_DeptId
SQL Server – Add a Check Constraint to an Existing Table
Syntax
ALTER TABLE [TableName]
ADD CONSTRAINT [Check_Constraint_Name] CHECK (Check_Condition)
Example
ALTER TABLE Employee
ADD CONSTRAINT CK_Employee_Salary CHECK (Salary > 0)
SQL Server – Drop Check a Constraint on an Existing Table
Syntax
ALTER TABLE [TableName]
DROP CONSTRAINT [Check_Constraint_Name]
Example
ALTER TABLE Employee
DROP CONSTRAINT CK_Employee_Salary
SQL Server – Add a NULL or NOT NULL Constraint to an Existing Table
Syntax
ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [DataType] [NULL OR NOT NULL]
Example
ALTER TABLE Employee
ALTER COLUMN ContactNo VARCHAR(10) NULL
SQL Server – Add a Default Constraint to an Existing Table
Syntax
ALTER TABLE [TableName]
ADD CONSTRAINT [DefaultConstraintName]
DEFAULT [DefaultValue] FOR [ColumnName]
Example
ALTER TABLE Employee
ADD CONSTRAINT DF_Employee_Active
DEFAULT 0 FOR ACTIVE
SQL Server – Drop a Default Constraint on an Existing Table
Syntax
ALTER TABLE [TableName]
DROP CONSTRAINT [Default_ConstraintName]
Example
ALTER TABLE Employee
DROP CONSTRAINT DF_Employee_Active
SQL Server – Change the Collation of an Existing Column
Syntax
ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [DataType] COLLATE [CollationName];
Example
ALTER TABLE Employee
ALTER COLUMN EmployeeName varchar(25) COLLATE Latin1_General_CI_AS NOT NULL;
SQL Server – Disable a Foreign Key Constraint
Syntax
ALTER TABLE [TableName]
NOCHECK CONSTRAINT [ConstraintName];
Example
ALTER TABLE Employee
NOCHECK CONSTRAINT Fk_Employee_DeptId;
SQL Server – Disable a Check Key Constraint
Syntax
ALTER TABLE [TableName]
NOCHECK CONSTRAINT [ConstraintName];
Example
ALTER TABLE Employee
NOCHECK CONSTRAINT CK_Employee_Salary;
SQL Server- Rename a Column in a Table
Syntax
EXEC sp_RENAME TableName.OldColumnName, NewColumnName, ColumnName
Example
EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'