Avoiding error message regarding Default value

Transact-SQL
1

I have a query part below:

IF COL_LENGTH('DRActivitiesMaster', 'IsBCMApproved') IS NOT NULL
BEGIN
	
	ALTER TABLE DRActivitiesMaster
	ADD DEFAULT 0 FOR IsBCMApproved 

	Print 'IsBCMApproved column modified in DRActivitiesMaster Table'
END
ELSE
BEGIN
	Print 'IsBCMApproved column not exists on DRActivitiesMaster Table';
END

I am getting a error message as below, when I run the query multiple times which is irritating:

Msg 1781, Level 16, State 1, Line 18
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 18
Could not create constraint or index. See previous errors.

How to add a condition that avoids this error?

2

Can you provide the DDL for DRActivitiesMaster

3

You are checking for the existence of the column - and if it exists attempting to add a default. Once the default has been added - you are going to get an error.

What you need to check for is the existence of the table - if it exists, check for the existence of the default constraint and add the constraint if it doesn't exist.

You can use sys.default_constraints to check for the default constraint.

4 
IF COL_LENGTH('DRActivitiesMaster', 'IsBCMApproved') IS NOT NULL
BEGIN
	IF EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = OBJECT_ID('DRActivitiesMaster') 
    AND c.name = 'IsBCMApproved' AND c.default_object_id = 0) /*0 means no default exists yet for this column*/
    BEGIN

	ALTER TABLE DRActivitiesMaster
	ADD DEFAULT 0 FOR IsBCMApproved 

	Print 'IsBCMApproved column modified in DRActivitiesMaster Table'
    END /*IF*/
    ELSE
    Print 'IsBCMApproved column already has a default value assigned to it';
END /*IF*/
ELSE
    Print 'IsBCMApproved column not exists on DRActivitiesMaster Table';
1 Like