CODEWITHSUNDEEP
sqlt-sql
Dimo
Dimo

Reputation: 3290

Dropping table in SQL with Constraint

I'm trying to drop a table in Northwind, and I'm getting:

ALTER TABLE DROP COLUMN Region failed because one or more objects access this column.

I'm using:

use [NorthWind]
go

alter table dbo.Customers
drop column Region

I guess it's because there is a constraint on the column Region. How do I find out which constraint I need to remove?

Upvotes: 1

Views: 2084

Answers (4)

Jose Cordero
Jose Cordero

Reputation: 526

First, you should know the ForeignKey - Constraint. After that, you must drop it.

Something like this:

-- looking for the Constraint's name related the dbo.Customers table
SELECT Table_Name,Constraint_Name
FROM Information_Schema.CONSTRAINT_TABLE_USAGE
WHERE Table_Name 'Customers'

-- Drop/Delete the founded constraint
ALTER TABLE [dbo].[Customers] DROP CONSTRAINT [FOREIGN_KEY_NAME]

Then, you'll run your actual script.

Upvotes: -1

vhadalgi
vhadalgi

Reputation: 7189

If you want to go via Sql Server Management Studio on the object explorer window, right click on the object you want to drop,then click view dependencies.

Upvotes: 0

TheTechGuy
TheTechGuy

Reputation: 17384

You must drop Constraint first and then drop the table.If you have SQL Server Management Studio you can select the constraint and delete it using GUI. Or you can use command line to drop your constraints

How to remove foreign key constraint in sql server?. Taken from this answer

ALTER TABLE <TABLE_NAME> DROP CONSTRAINT <FOREIGN_KEY_NAME>

Upvotes: 1

Aditya
Aditya

Reputation: 2311

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

OR

ALTER TABLE table_Name NOCHECK CONSTRAINT all

OR

ALTER TABLE table_Name NOCHECK CONSTRAINT constraint_name

Then try with your SQL.

Please use this when you really want to drop constraints no matter other tables affected.

If disabling the constraints is not enough, you will have to drop the constraints.

Upvotes: 1

Related Questions