It’s very easy to drop a constraint on a column in a table. This is something you might need to do if you find yourself needing to drop the column, for example. SQL Server simply will not let you drop a column if that column has any kind of constraint placed on it.
In this very brief tutorial, we’ll discuss how to drop a constraint on a SQL Server table.
Just run the DROP CONSTRAINT statement in an ALTER TABLE statement
Take a look at the following table:
CREATE TABLE Books ( BookID INT IDENTITY(10,5) PRIMARY KEY, Title VARCHAR(35) UNIQUE, Author VARCHAR(15), Pages INT, DateAdded DATETIME DEFAULT GETDATE() )
Notice there are several constraints placed on this table:
- Primary Key constraint on the BookID column
- Unique constraint on the Title column
- Default constraint on the DateAdded column
We’ll use the default constraint on the DateAdded column as an example. This constraint is in place so that we don’t need to worry about specifying a value whenever a new row is inserted. The new row will automatically get a value of the date the INSERT was performed. For example, here’s a new row getting inserted into the table where the DateAdded column is automatically given a value of 6/18/2022, 7:32 AM (which is the date and time of this writing):
An example of dropping a constraint
Let’s say we want to drop the DateAdded column. If we run a DROP COLUMN statement, we get an error message:
The error very clearly outlines that we cannot delete this column because there is an object that references this column (that object being the default constraint). SQL Server wants you to delete the constraint first before it will let you delete the column.
Ok, so to drop the constraint, we would follow this syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name
We know our table name is ‘Books‘, but what about the constraint name? Well the error message we saw a moment ago very clearly outlines the name of the constraint, so we could simply copy and paste from that error message.
You can also see the names of your constraints in the object explorer. For example, we can see the name of our default constraint under the Constraints folder:
This is also where Check Constraints would appear if there were any established for the table.
Primary Key, Foreign Key, and Unique constraints all appear under the Keys folder. For example, here are the Primary Key and Unique constraints for our Books table:
Going back to our example, if we want to delete the default constraint on the DateAdded table, we would run the following statement:
If we look back at our Constraints folder, the constraint is gone:
Now that the constraint is gone, we should be able to delete the DateAdded column:
That’s it!
Next Steps:
Leave a comment if you found this tutorial helpful!
Constraints are very common and useful tools in Microsoft SQL Server that you should definitely understand. If you want to understand the basics of all the different constraints, you should download the following FREE Ebook:
FREE Ebook on SQL Server Constraints!
This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:
- Primary Key constraints
- Foreign Key constraints
- Default constraints
- Check constraints
- Unique constraints
Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
