Delete column with constraints

[Mack2]

I have a lot of columns with constraints to delete. Is there a way to have the code still delete the column even if it has a constraint? Or do I have to go through and delete every constraint first, then delete the column.
Thanks in advance!

 

[tlbroadbent]

See ALTER TABLE in SQL BOL. You can disable/enable all foreign key and check constraints on a table.

--disable constraints
ALTER TABLE YourTable NOCHECK CONSTRAINT ALL

<delete rows>

--enable constraints
ALTER TABLE YourTable CHECK CONSTRAINT ALL

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Mack2]

I did try that, I took code from bol and I received an error. It seems like what you suggested will work. I dont know if I have the code wrong
Thanks for your help, and nice web site!
Stacy

 

[tlbroadbent]

What was the error? Please post your code. And thanks. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Mack2]

This is my code, it may be something very basic that I am missing, I am new to transact sql

ALTER TABLE doc_exb DROP COLUMN column_b
NOCHECK CONSTRAINT ALL
GO

Thanks in advance!

 

[tlbroadbent]

I apologize. I thought you were deleting rows from a table. If you are dropping a column from a table you must first drop the constraint. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm