SQL ERROR : Column already has a DEFAULT bound to it-CodePudding

I have a table as below with a default value column as [IsProcessed]

IF NOT EXISTS (SELECT * FROM sys.objects 
               WHERE object_id = OBJECT_ID(N'[dbo].[Kovair_JileEventHistory]') 
                 AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[Kovair_JileEventHistory]
    (
        [Id] [bigint] IDENTITY(1,1) NOT NULL,
        [GeneratedActionId] [char](36) NOT NULL,
        [BaseUrl] [nvarchar](255) NOT NULL,
        [ProjectName] [nvarchar](255) NOT NULL,
        [EntityName] [varchar](255) NOT NULL,
        [EntityId] [varchar](255) NOT NULL,
        [RelatedEntityName] [varchar](255) NOT NULL,
        [RelatedEntityId] [varchar](255) NOT NULL,
        [ActionName] [varchar](255) NOT NULL,
        [LoopbackEventName] [varchar](255) NOT NULL,
        [LastUpdatedOnGMT] [varchar](50) NOT NULL,
        [LastUpdatedBy] [nvarchar](255) NOT NULL,
        [IsProcessed] [char](1) NULL,

        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.objects 
               WHERE object_id = OBJECT_ID(N'[dbo].[DF__Kovair_Ji__IsPro__5224328E]') 
                 AND type = 'D')
BEGIN
    ALTER TABLE [dbo].[Kovair_JileEventHistory] 
        ADD DEFAULT ('N') FOR [IsProcessed]
END

When I am executing this lines in a different system if I execute it once its working fine but on the second time when I am executing it its giving me an error as below.

Msg 1781, Level 16, State 1, Line 26
Column already has a DEFAULT bound to it.

Msg 1750, Level 16, State 0, Line 26
Could not create constraint. See previous errors.

But when ever I am doing the same in my system from where I have generated this script I am able to execute it multiple time without any error.

I think

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__Kovair_Ji__IsPro__5224328E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Kovair_JileEventHistory] ADD  DEFAULT ('N') FOR [IsProcessed]
END

in this lines the part where OBJECT_ID(N'[dbo].[DF__Kovair_Ji__IsPro__5224328E]') is system specific.

Is there any way to solve this issue?

CodePudding user response：

This is flawed:

IF NOT EXISTS 
(
  SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[DF__Kovair_Ji__IsPro__5224328E]') 
  AND type = 'D'
)

For a few reasons:

You let the system name the constraint, which means every time you create the table, that system will come up with a new name. You should always explicitly name your constraints so that scripts like this can be made more predictable and so that you can have a sane naming convention.
```
 ALTER TABLE dbo.tablename 
   ADD CONSTRAINT DF_SomethingSmarter
   DEFAULT('N') FOR IsProcessed;
```
You're basically just saying "is there a default constraint on this table with this name" but you've hard-coded the constraint name. It's possible this could behave differently on a different system for a different reason - say there is another column that starts with IsPro... and it happens to get the same quasi-random system-generated constraint name.

If you know what column and what type of constraint, a much safer (and admittedly more verbose) check is to ignore the constraint name and go after the table column name:

 IF NOT EXISTS 
 (
   SELECT 1 FROM sys.default_constraints AS dc
    WHERE parent_object_id = OBJECT_ID(N'dbo.Kovair_JileEventHistory')
    AND EXISTS
    (
       SELECT 1 FROM sys.columns AS c 
        WHERE c.object_id = dc.parent_object_id
          AND c.column_id = dc.parent_column_id
          AND c.name = N'IsProcessed')
    )
 )
 BEGIN
   -- now I know there is no default constraint
   -- on this column, no matter what it's named
 END

I would even argue for something more verbose, since at scale some of the metadata functions do not obey isolation semantics, which might be problematic depending on the scope of the change and the recoverability of the entire script:

 IF NOT EXISTS 
 (
   SELECT 1 FROM sys.default_constraints AS dc
    INNER JOIN sys.tables AS t
      ON t.[object_id] = dc.parent_object_id
      AND t.name = N'Kovair_JileEventHistory'
      AND t.[schema_id] = 1
    WHERE EXISTS
    ...