SQL Server Drop Column with Constraint

by

In this SQL Server tutorial, I will explain the SQL Server drop column with constraint.

Sometimes, you need to drop a column when it is no longer needed, but if any constraint depends on the column you want to drop, you may not be able to drop that column, and it will show an error when you try to drop it.

As a database developer, I have encountered this situation many times. To resolve this issue, I always follow the procedure to drop the column with a constraint, which I have explained in this tutorial, so you are ready to deal with it when you encounter it.

Let’s start,

SQL Server Drop Column with Constraint

To drop a column with a constraint, you must use two commands in a sequence: first, you must drop the constraint and then the column. You can’t do it with a single command.

Below, I have given the syntax that you use in sequence to drop columns with constraints.

The syntax to drop the constraint is given below.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Where,

  • ALTER TABLE: This command allows you to change the properties of the table.
  • table_name: Name the table containing the constraint you want to drop.
  • DROP CONSTRAINT: This is the command to drop the constraint.
  • constraint_name: Name the constraint on the column you want to drop.

The syntax to drop the column is given below.

ALTER TABLE table_name
DROP COLUMN column_name;
  • ALTER TABLE: This command allows you to change the properties of the table.
  • table_name: Name the table containing the column you want to drop.
  • DROP COLUMN: This command drops the specified column from the table.
  • column_name: Name of the table column that you want to drop.

Now, before dropping the column with constraint, let’s create two tables: Employees and Departement.

CREATE TABLE Departments (
	DepartmentID INT PRIMARY KEY,
	DepartmentName VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
	EmployeeID INT PRIMARY KEY,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	DepartmentID INT, 
	CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
	);

If you look at the column DepartmentID, this column is used in the foreign key constraint.

Creating Table Department and Employees Containing Constraint Column

Dropping the column ‘DepartmentID’ directly using the DROP COLUMN command, as shown below, shows an error.

ALTER TABLE Employees
DROP COLUMN DepartmentID;
SQL Server Drop Column with Constraint Error

Look at the output; when you try to drop the column DepartmentID, it shows the error ‘The object ‘FK_Employees_DepartmentID’ is dependent on column ‘DepartmentID’ and ‘ALTER TABLE DROP COLUMN DepartmentID failed because one or more objects access this column’.

So, to drop the column ‘DepartementID’ with constraint ‘FK_Employees_DepartmentID’, first drop this constraint using the code below.

ALTER TABLE Employees
DROP CONSTRAINT FK_Employees_DepartmentID;

Then, after dropping the constraint, drop the column ‘DepartmentID’ using the code below.

ALTER TABLE Employees
DROP COLUMN DepartmentID;

After executing the above two commands in sequence, view the Employees table using the query below.

SELECT * FROM Employees;
SQL Server Drop Column with Constraint

Look at the output; the table ‘Employees’ only contains three columns: EmployeeID, FirstName and LastName, and the DepartementID column is dropped.

Following the above procedure, I hope you understand how to drop columns with constraints using the DROP CONSTRAINT and DROP COLUMN commands.

Conclusion

In this SQL Server tutorial, you learned to drop columns with constraints using the sequence of DROP CONSTRAINT and DROP COLUMN commands.

You learned that to drop a column with a constraint, you must first delete the constraint and then the column.

You may like to read: