Basics of SQL Server ALTER TABLE Statement

This article focuses on the ALTER TABLE statement in SQL Server and the following tasks on SQL Server tables:

1. Add one or multiple columns to an existing table.
2. Modify one or multiple columns of a table, particularly:
   • Add constraints on the column.
   • Change the data type of the column.
3. Drop the column of the SQL table.

For demonstration purposes, I have created a database named VSDatabase that will insert the patients’ data. There, I have added a table named tblPatients. The table structure is as follows:

The following is the script to create the table:

Use VSDatabase
Go
create table tblpatients
(
Patient_ID varchar(10),
Patient_name varchar(50),
Address varchar(500),
City varchar(50),
AppointmentDate datetime
)

The following script inserts dummy data into the tblPatients table:

insert into tblpatients (Patient_ID,Patient_name,Address,City,AppointmentDate)
values
('OPD00001','Nisarg Upadhyay','AB-14, Akshardham Flats','Mehsana','2021-02-10'),
('OPD00002','Nirali Upadhyay','AB-14, Akshardham Flats','Mehsana','2021-02-11'),
('OPD00003','Manushi Bhatt ','B-50, Suramya Flats','Surat','2021-02-10'),
('OPD00004','Arjun Bhatt','B-50, Suramya Flats','Surat','2021-02-12'),
('OPD00005','Dixit Upadhyay','AB-14, Akshardham Flats','Mehsana','2021-02-09')

Now, let us understand the ALTER TABLE statement by examples.

Add one or multiples columns to an existing table

We can add one or multiple columns using the ALTER TABLE command. The syntax to add the columns is as follows:

ALTER TABLE tbl_name ADD column_name_1 datatype, column_name_2 datatype ..

Where,

1. tbl_name: specify the name of the table where you want to add a new column.
2. Column_name_1 datatype: specify the column name and its data type. When you add multiple columns, each of them must be separated by a comma (,)

Example

I want to add a new column named first_name to tblPatients. To add the column, we run the following query:

ALTER TABLE tblpatients ADD first_name VARCHAR(50)

Now we add multiple columns, the middle_name and last_name columns to the tblPatients table. To do that, we run the following query:

ALTER TABLE tblpatients ADD middle_name VARCHAR(50),last_name VARCHAR(50)

We can query information_schema.columns or sys.columns tables to check the newly added columns. The queries are the following:

To query information_schema.columns:

select TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblpatients'

Output:

To query Sys.columns:

select object_name(object_id) [Table Name],name,max_length from sys.columns where object_id =object_id('tblpatients')

Output:

As you can see, the columns have been added.

Modify the Columns of a Table

Now we want to explore modifying columns of a table. First, we have to understand how we can change the name of the table’s column.

Change the Data Type of the Existing Table

The syntax of ALTER TABLE to change the datatype of the column is:

ALTER TABLE tbl_name ALTER COLUMN column_name datatype

Where,

• tbl_name: the name of the table where you need to change the column’s datatype.
• Column_name: the column name and the data type. We must specify the new data type after the ALTER COLUMN keyword.

Let us change the datatype of the address column. The current length of the Address column is varchar(1000). We must change the column length to varchar(50).

The query to modify the data type is as follows:

Alter table tblpatients alter column address varchar(50)

When we run the SQL query, we receive the following error:

Msg 2628, Level 16, State 1, Line 1
String or binary data would be truncated in table 'VSDatabase.dbo.tblpatients', column 'Address'. Truncated value: ''.

So, while reducing the column’s length, we must review the maximum length of the record and, based on that, change it. In our case, we run the following query to identify the maximum length of the record in the Address column:

select len(Address) [Maximum length of column],Address from tblpatients

Output:

As per output, the minimum length of the address column must be varchar(25).

Run the following query to change the length of the column:

Alter table tblpatients alter column address varchar(25)

Run the following query to verify the length:

select TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tblpatients'

Output:

As you can see, the column length has been changed successfully.

Add Constraint on the Column

We can add a constraint to the column by using the ALTER TABLE statement. The ALTER TABLE syntax is as follows:

ALTER TABLE tbl_name ALTER COLUMN column_name datatype constraint_name

Where,

• tbl_name: the name of the table where you need to change the column’s datatype.
• Column_name: the column name and the data type. Specify the new datatype after ALTER COLUMN keyword.
• Constraint_name: the type of constraint. It must be any of the following constraints:
  • UNIQUE
  • NOT NULL
  • CHECK
  • DEFAULT
  • PRIMARY KEY
  • FOREIGN KEY

Suppose you want to add a NOT NULL constraint on the city column. Run the following ALTER TABLE statement:

Alter table tblpatients alter column Patient_name varchar(50) not null

The syntax of adding the PRIMARY KEY constraint is different if you want to add a primary key on the patient_id column. To add the constraint, execute the following query:

Alter table tblpatients add primary key (Patient_ID)

You will receive the following error:

Msg 8111, Level 16, State 1, Line 17
Cannot define PRIMARY KEY constraint on nullable column in table 'tblpatients'.
Msg 1750, Level 16, State 0, Line 17
Could not create constraint or index. See previous errors.

To fix the error, we must create a NOT NULL constraint on the Patient_ID column. Run the following query:

Alter table tblpatients alter column Patient_ID varchar(50) not null

Once the constraint is added, we add the primary key by running the following query:

Alter table tblpatients add primary key (Patient_ID)

The primary key will be added successfully.

To view the constraint on the table, run the following query:

SELECT OBJECT_NAME(object_id) AS ConstraintName,
SCHEMA_NAME(schema_id) AS SchemaName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' AND OBJECT_NAME(parent_object_id)='tblpatients'

Output:

This way, the Primary key constraint has been created.

Drop a Column of the Table

We can drop a column using the ALTER TABLE statement. The syntax is:

ALTER TABLE tbl_name DROP Column column_name

Where,

• tbl_name: the name of the table where you want to drop a column.
• Column_name: the column that you want to drop from the table.

We have added the first_name and last_name columns to the tblPatients table. Hence, we do not require the patient_name column.

To drop the column, run the following query:

Alter table tblpatients drop column Patient_name

Note: If you are dropping a column with a PRIMARY KEY or FOREIGN KEY constraint, you must drop the constraint before dropping the column.

Summary

The article has explored the purpose of the ALTER TABLE statement and the basic use cases. We can add, modify, and drop columns of the table, as well as add constraints in a table using the ALTER TABLE statement. In the next article, I will cover some advanced managing options.

Read Also

Understanding SQL Server ALTER TABLE ADD COLUMN Statement

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