ALTER TABLE usages in MS SQL Server

By | 26th July 2019

In SQL server, ALTER TABLE statement is used for the following purposes 

  1. ADD columns/ constraints in the existing table. 
  2. DROP columns/ constraints in the existing table. 
  3. ALTER columns in the existing table. 
  4. ENABLE/ DISABLE triggers.

ADD columns/constraints

Syntax to add columns and constraints while creating column

ALTER TABLE tableName 
ADD columnName1 datatype,  
columnName2 datatype CONSTRAINT constraintName constraintType

Syntax to add column(s)

ALTER TABLE tableName 
ADD columnName1 datatype, columnName2 datatype

One or more columns can be added at once using the above syntax. 

Syntax to add constraint(s) to the existing column

ALTER TABLE tableName 
ADD CONSTRAINT constraintName1 constraintType, CONSTRAINT constraintName2 constraintType

constraintType – Type of constraints like Primary Key, Default, etc.

To add constraint(s) to the existing column, use the ADD CONSTRAINT keyword in the ALTER TABLE statement.

Points to note when adding columns and constraints:

  • non-nullable columns can be added only when the table is empty.  
  • To add a non-nullable column when the table isn’t empty, the column must be added with a default constraint like below
ALTER TABLE tableName 
ADD columnName DATETIME NOT NULL  
CONSTRAINT DF_tableName_columnName DEFAULT GETDATE()
  • When adding a nullable column with a default constraint and the table isn’t empty, define default constraint WITH VALUES to update the column in the existing rows in the table with default values.
ALTER TABLE tableName 
ADD columnName DATETIME NOT NULL  
CONSTRAINT DF_tableName_columnName DEFAULT GETDATE() WITH VALUES

Let us see some of the examples to understand this better

ALTER TABLE tblPlayer 
ADD battingStyle VARCHAR(10), modifiedDate DATETIME

The above query adds the ‘battingStyle’ and ‘modifiedDate’ columns to the table ‘tblPlayer’. Both the columns are NULL by default.

ALTER TABLE tblPlayer 
ADD CONSTRAINT DF_tblPlayer_modifiedDate DEFAULT GETDATE() FOR modifiedDate

The above query adds the default constraint for the column ‘modifiedDate’.

ALTER TABLE tblPlayer 
ADD battingStyle VARCHAR(10), modifiedDate DATETIME CONSTRAINT DF_tblPlayer_modifiedDate DEFAULT GETDATE() WITH VALUES

The above query adds the columns ‘battingStyle’ and ‘modifiedDate’ to the table ‘tblPlayer’. It also adds the default constraint to the column ‘modifiedDate’. 

DROP columns/constraints

Similar to adding a new column, the ALTER TABLE statement is also used to DROP the columns and constraints. A column can’t be dropped when it has any constraints or indexes. Before dropping the column, drop all the constraints first.

Syntax to drop constraints and columns

ALTER TABLE tableName 
DROP CONSTRAINT constraintName, COLUMN columnName

Syntax to drop column(s) and constraint(s) in a separate queries

ALTER TABLE tableName 
DROP COLUMN columnName
ALTER TABLE tableName 
DROP CONSTRAINT constraintName

Now, let us see some examples for the above syntaxes to understand.

ALTER TABLE tblPlayer 
DROP CONSTRAINT DF_tblPlayer_modifiedDate

The above query drops the default constraint ‘DF_tblPlayer_modifiedDate’ on the column ‘modifiedDate’.

ALTER TABLE tblPlayer 
DROP COLUMN modifiedDate 

The above query drops the column ‘modifiedDate’ from the table ‘tblPlayer’.

ALTER columns

In ALTER TABLE statement, ALTER COLUMN keyword is mainly used to,

  • Modify the data type of a column. 
  • Change the size of a column.  
  • Change NULL to NOT NULL or vice versa. 

It is not possible to change the column name using ALTER TABLE statement.

Syntax to alter column in a table

ALTER TABLE tableName 
ALTER COLUMN columnName datatype 

Here are some different cases of using Alter column statement

ALTER TABLE tblPlayer 
ALTER COLUMN battingStyle VARCHAR(15) 

The above query increases the size of ‘battingStyle’ column from 10 to 15.

ALTER TABLE tblPlayer 
ALTER COLUMN battingStyle VARCHAR(15) NOT NULL

The query alters the ‘battingStyle’ column from NULL to NOT NULL.

ALTER TABLE tblPlayer 
ALTER COLUMN battingStyle NVARCHAR(15)

Above query alters the datatype of ‘battingStyle’ column from VARCHAR to NVARCHAR.

Here are some important points to consider while using ALTER statement

  • A column can’t be dropped if it has constraints or indexes.  
  • Primary key or foreign key column can’t be altered. 
  • Changing the primary key column from NOT NULL to NULL is not allowed. 
  • The previous data type must be implicitly convertible to the new data type. 
  • Altering the size of a column when the table isn’t empty, the new size should be greater than the current size. 
  • The current data type of the column must be implicitly convertible to the new data type. 
  • A column with a timestamp data type can’t be altered.

To alter a NULL column to NOT NULL when the table isn’t empty, update the null values with some values and alter the column as NOT NULL.

UPDATE tableName SET columnName = ' ' WHERE columnName IS NULL; 
ALTER TABLE tableName ALTER COLUMN columnName VARCHAR(20) NOT NULL;

ENABLE/ DISABLE triggers

ALTER TABLE statement can also be used to enable and disable a trigger or all triggers in a table.

Syntax to enable a trigger(s) in a table

--Enable a trigger
ALTER TABLE tableName
ENABLE TRIGGER triggerName;

--Enable All triggers
ALTER TABLE tableName
ENABLE TRIGGER ALL;

Syntax to disable a trigger(s) in a table

--Disable a trigger
ALTER TABLE tableName
DISABLE TRIGGER triggerName

--Disable All triggers
ALTER TABLE tableName
DISABLE TRIGGER ALL

Also, Check the article on CREATE TABLE

References: ALTER TABLE (Transact-SQL)