Drop a Constraint doesn’t work

Question

I am getting following error when deleting a column with constraint in SQL Server 2005.

The object 'DF__PlantRecon__Test' is dependent on column 'Test'.

The column is not part of any key. But it has a default constraint and the constraint has a pre-defined name.

Though I have written code to delete constraint first, it is not working.

Why doesn't it work?
What need to be done to make it working?

Note: I need to check whether the constraint exist first.

REFERENCES

CODE

IF OBJECT_ID('DF__PlantRecon__Test', 'C') IS NOT NULL 
BEGIN
    SELECT 'EXIST'
    ALTER TABLE [dbo].[PlantReconciliationOptions] drop constraint DF__PlantRecon__Test
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='DF__PlantRecon__Test')
BEGIN

    SELECT 'EXIST'
    --drop  constraint
    ALTER TABLE [dbo].[PlantReconciliationOptions] drop constraint DF__PlantRecon__Test
END

IF EXISTS ( SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[dbo].[PlantReconciliationOptions]') AND name = 'Test')
BEGIN
    --drop column
    ALTER TABLE [dbo].[PlantReconciliationOptions] DROP COLUMN Test
END

ALTER TABLE PlantReconciliationOptions
ADD Test INT NOT NULL 
CONSTRAINT DF__PlantRecon__Test  DEFAULT 30

(Why is the constraint check/drop done twice?) – user2864740 May 08 '14 at 19:39 — user2864740, May 08 '14 at 19:39

score 3 · Accepted Answer · edited May 08 '14 at 20:07

try

IF OBJECT_ID('DF__PlantRecon__Test') IS NOT NULL 
BEGIN
    SELECT 'EXIST'
    ALTER TABLE [dbo].[PlantReconciliationOptions] drop constraint DF__PlantRecon__Test
END

In your example, you were looking for a 'C' Check Constraint, which the DEFAULT was not. You could have changed the 'C' for a 'D' or omit the parameter all together.

If you are consistent with your naming, as it appears you are, (e.g. DF__xxx) then dropping the second parameter is an acceptable choice to be sure that the hard coded constraint name is dropped.

Here is a list of the OBJECT_ID() Object Types you can pass:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

(This list was found on Beyond Relational: Using TSQL Function: OBJECT_ID())

the command OBJECT_ID('DF__PlantRecon__Test') will return a value where OBJECT_ID('DF__PlantRecon__Test', 'C') will not. Therefore the default constraint WILL be deleted in this case. — RosSQL, May 08 '14 at 19:43

score 1 · Answer 2 · answered May 08 '14 at 19:27

1

Steps to solve your problem.

Open SSMS.
Edit the table to remove the default. Do not press save
Script the output to "new window".
Read the script.

answered May 08 '14 at 19:27

Ben

34,935
6
74
113

That won't help. I need to check whether it exist first. – LCJ May 08 '14 at 19:30

Drop a Constraint doesn’t work

2 Answers2