Understanding SQL Server ALTER TABLE ADD COLUMN Statement

This article describes the SQL Server ALTER TABLE ADD COLUMN statement. The purpose of this statement is to add a new column to an existing table. Also, the article will clarify the following use cases:

1. Add multiple columns.
2. Add a new primary key column.
3. Add a new foreign key column.
4. Add new columns that have computed columns.
5. Add a new column that has CHECK and UNIQUE constraints.

The Syntax Specificity of the SQL ALTER TABLE ADD COLUMN Statement

As we are going to explore the statement thoroughly, let’s start with understanding the syntax:

Alter table [table_name] add column [column_name] [data_type][constraint]

Note the following aspects:

1. You need to specify the name of the table where you want to add a column after ALTER TABLE.
2. The name of the column or columns must be after ADD. For multiple columns, separate their names with commas.
3. Specify the column data types after the column name.
4. If you are adding any constraint, a primary key, or a foreign key, specify it after the data type.

For demonstration purposes, I have created a database named CodingSight. There, I have created three tables named tbldepartment, tblemployee, and tblCity, and inserted dummy data into those tables.

The following script creates a database and tables:

USE codingsight
GO
CREATE TABLE tbldepartment(departmentID INT IDENTITY(1,1), departmentname VARCHAR(50), 
CONSTRAINT PK_departmentID  PRIMARY KEY  (departmentID))
GO
CREATE TABLE tblemployee(employeeID INT IDENTITY(1,1), name VARCHAR(50), Address VARCHAR(max), PhoneNumber VARCHAR(10)
CONSTRAINT PK_employeeID  PRIMARY KEY  (employeeID))
GO
CREATE TABLE tblCity( Name VARCHAR(50), Country VARCHAR(100))
GO

The below script inserts the dummy data into the table:

INSERT INTO tbldepartment (departmentname) VALUES ('IT'),('Devlopment Team'),('Human Resource'),('Qualiy Assurance')
GO
INSERT INTO tblemployee (name, address, PhoneNumber) VALUES 
('Nisarg Upadhyay','AB14, Akshardham Flats, Mehsana', 1234567890),
('Nirali Upadhyay','AB05, Suyojan Flats, Surat', 1234567890),
('Dixit Upadhyay','B14, Suyash Flats, Vadodara', 1234567890),
('Bharti Upadhyay','C14, Suramya Flats, Ahmedabad', 1234567890),
('Sawan panchal','P18, Kirti Flats, Ahmedabad', 1234567890),
('Raghav Dave','A14, Suramya Flats, Ahmedabad', 1234567890)
GO
INSERT INTO tblCity (name, country)VALUES 
('Ahmedabad','India'),
('Surat','India'),
('Vadodara','India'),
('Gandhinagar','India')
Go

Now, let us proceed to the use cases.

Add Multiple Columns to a Table

In our example, we want to add the State and Pincode columns to the tbleCity table. The datatype of the State column is VARCHAR, and the length of the column is 150. The datatype of the Pincode column is INTEGER. To add columns, run the following query:

ALTER TABLE tblCity ADD State VARCHAR(150), Pincode INT

Now, run the query to view the column list of the tblCity table:

USE CodingSight
go
SELECT c.TABLE_SCHEMA,c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS c WHERE C.TABLE_NAME='tblCity'

The Query Output:

As you can see, the State and Pincode columns have been added.

Add Primary Key Columns to a Table 

Let us add the Primary Key column to the tblCity table.  According to the business requirements, we must add a column with a combination of a character and a four-digit number to identify the city.

We are adding a new column named City_Code to the tblCity table. The datatype of the City_Code is varchar, and the length is five. Run the following query to add a column:

ALTER TABLE tblCity ADD City_Code VARCHAR(5) PRIMARY KEY  CLUSTERED

The output:

Msg 4901, Level 16, State 1, Line 29
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column. 
Alternatively, if none of the previous conditions are satisfied the table must be empty to allow the addition of this column. 
Column 'City_Code' cannot be added to non-empty table 'tblCity' because it does not satisfy these conditions.

The query returns an error because the primary key must be unique and not null. To add a column to this table, we must follow the below steps:

Step:1

Create a new empty table named tblCity_Temp using the definition of the existing tblCity table. The new table has a primary key column.

The creation script is as follows: 

CREATE TABLE tblCity_Temp( City_code VARCHAR(5) CONSTRAINT PK_City_Code PRIMARY KEY CLUSTERED,
Name VARCHAR(50), Country VARCHAR(100))
GO

Step:2

Insert the data into the tblCity_Temp table from the tblCity table:

INSERT INTO tblCity_Temp (City_code,name, country)VALUES 
('C0001','Ahmedabad','India'),
('C0002','Surat','India'),
('C0003','Vadodara','India'),
('C0004','Gandhinagar','India')
GO

Step: 3

Rename the tables as shown below:

1. Old Name: tblCity – New Name: tblCity_Old.
2.  Old Name: tblCity_Temp – New Name: tblCity.

Pay attention to the table-renaming query:

USE codingsight 
go 

EXEC Sp_rename 
  'tblCity', 
  'tblCity_Old' 
go 

EXEC Sp_rename 
  'tblCity_Temp', 
  'tblCity' 
go

Now, run the following query to view the newly added primary key:

USE codingsight 

go 

SELECT Schema_name(tbl.schema_id)    AS [schema_name], 
       primary_key.[name]            AS pk_name, 
       index_columns.index_column_id AS column_id, 
       TABLE_coumns.[name]           AS column_name, 
       tbl.[name]                    AS table_name 
FROM   sys.tables tbl 
       INNER JOIN sys.indexes primary_key 
               ON tbl.object_id = primary_key.object_id 
                  AND primary_key.is_primary_key = 1 
       INNER JOIN sys.index_columns index_columns 
               ON index_columns.object_id = primary_key.object_id 
                  AND index_columns.index_id = primary_key.index_id 
       INNER JOIN sys.columns TABLE_coumns 
               ON primary_key.object_id = TABLE_coumns.object_id 
                  AND TABLE_coumns.column_id = index_columns.column_id 
                  AND tbl.NAME = 'tblCity' 
ORDER  BY Schema_name(tbl.schema_id), 
          primary_key.[name], 
          index_columns.index_column_id  

The Output:

Use the query for the data viewing:

SELECT * FROM tblCity c

The Output:

Add Foreign Key Columns to a Table

Suppose we want to add a column named deptID to the tblEmployee table. The data type of the column is INTEGER. It references the departmentID column of the tbldepartment table.

The query is the following:

ALTER TABLE tblEmployee ADD deptID INTEGER,CONSTRAINT  FK_DepartmentID FOREIGN KEY(deptID) REFERENCES tbldepartment(departmentID)

Run the next query to view the new foreign key column:

USE codingsight 
go 

SELECT Schema_name(tbl.schema_id) + '.' + tbl.NAME       AS foreign_table, 
       Schema_name(primarykey_tab.schema_id) + '.' 
       + primarykey_tab.NAME                             AS primary_table, 
       Substring(column_names, 1, Len(column_names) - 1) AS [fk_columns], 
       foreignkeys.NAME                                  AS fk_constraint_name 
FROM   sys.foreign_keys foreignkeys 
       INNER JOIN sys.tables tbl 
               ON tbl.object_id = foreignkeys.parent_object_id 
       INNER JOIN sys.tables primarykey_tab 
               ON primarykey_tab.object_id = foreignkeys.referenced_object_id 
       CROSS apply (SELECT col.[name] + ', ' 
                    FROM   sys.foreign_key_columns fk_columns 
                           INNER JOIN sys.columns col 
                                   ON fk_columns.parent_object_id = 
                                      col.object_id 
                                      AND fk_columns.parent_column_id = 
                                          col.column_id 
                    WHERE  fk_columns.parent_object_id = tbl.object_id 
                           AND fk_columns.constraint_object_id = 
                               foreignkeys.object_id 
                    ORDER  BY col.column_id 
                    FOR xml path ('')) D (column_names) 
ORDER  BY Schema_name(tbl.schema_id) + '.' + tbl.NAME, 
          Schema_name(primarykey_tab.schema_id) + '.' 
          + primarykey_tab.NAME   

The Output:

Note the ER Diagram:

This way, we’ve created a foreign key named FK_DepartmentID.

Add a Column with CHECK and DEFAULT Constraint

Now, let us add a column named minimum_wages to the tblemployee table. The data type of the column is money.

The minimum_wage column’s value must be greater or equal to 5.60$. Therefore, we’ve created the check condition to verify the inserted value – it must be higher than 5.60$. Moreover, the default value of the column is 5.60$.

To add the column, run the following query:

USE codingsight 
go 
ALTER TABLE tblemployee 
ADD minimum_wages MONEY CONSTRAINT chk_minimum_wages CHECK (minimum_wages>=5.6)
CONSTRAINT df_minimum_wages DEFAULT 5.6;  
Next, we run the UPDATE query to change the values:
UPDATE tblemployee SET minimum_wages=5.5 WHERE employeeID =5;

The following error occurs:

Msg 547, Level 16, State 0, Line 78
The UPDATE statement conflicted with the CHECK constraint "CHK_minimum_wages". 
The conflict occurred in database "CodingSight", table "dbo.tblemployee", column 'minimum_wages'.

The value specified for the minimum_wages column is less than 5.6. That’s why the query has returned an error.

Change the value of the minimum_wages value and execute the query again:

UPDATE tblemployee SET minimum_wages=5.8 WHERE employeeID =5;

The Output:

(1 row affected)
Completion time: 2020-12-14T17:22:40.8995851+05:30

Once we’ve updated the record, run the below query to view the data:

Select * from tblemployee where employeeID=5

The Output:

To verify that the default constraint is working, run the following INSERT statement:

USE codingsight 
GO
INSERT INTO tblemployee (name, Address, PhoneNumber,deptID) VALUES ('Dilip Patel','Satyamev Famosa, Gandhinagar','9537021112',1)

Once the record is inserted, let us view the data:

The Query:

USE CodingSight
go
SELECT * FROM tblemployee t WHERE t.employeeID=25

The Output:

I have not specified the value of the minimum_wages column. Hence, the default value is inserted in the table.

Summary

The article clarified the essence of the ALTER TABLE ADD COLUMN statement, its syntax, and various use cases of implementations.

Read Also

Basics of SQL Server ALTER TABLE Statement
Understanding SQL DELETE Column from table
Understanding DROP TABLE IF EXISTS SQL Statement

Tags: sql alter table add column, sql functions, sql server Last modified: September 17, 2021