In SQL server, ALTER TABLE statement is used for the following purposes
- ADD columns/ constraints in the existing table.
- DROP columns/ constraints in the existing table.
- ALTER columns in the existing table.
- 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)