Drop Column with Constraint

Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais

ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?

Seems your code is OK:

IF OBJECT_ID('T1') IS NOT NULL
drop table T1;

IF OBJECT_ID('T2') IS NOT NULL
drop table T2;

Create Table T1 (
num int primary key,
[Name] sysname
)

Create Table T2 (
Id int primary key,
num int,
Constraint T2_FK Foreign Key(num) References T1(num)
)

BEGIN TRANSACTION
ALTER TABLE T2 DROP CONSTRAINT T2_FK
ALTER TABLE T2 DROP COLUMN num;
COMMIT

The reason it require drop constraint first, is because T2_FK depends on column num,
Just like you can't drop a table if the table reference it not droped.

Thanks,

zuomin

|||

It should be work because the ALTER TABLE has an implicit transactions. More , I tested your situations and the things worked good. I thing your error come to the other part.

|||Yes my mistake. The error was coming from default constraint.. nevermind it! Tks for your help!