2

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.

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

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

REFERENCES

  1. Named CONSTRAINT benefits
  2. How to drop column with constraint?
  3. How to drop SQL default constraint without knowing its name?

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
Improve this question
1
  • (Why is the constraint check/drop done twice?) Commented May 8, 2014 at 19:39

2 Answers 2

Reset to default
3

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())

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
1

Steps to solve your problem.

  1. Open SSMS.
  2. Edit the table to remove the default. Do not press save
  3. Script the output to "new window".
  4. Read the script.
Improve this answer

1 Comment

That won't help. I need to check whether it exist first.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.