Looking for correct sql syntax to add default values to existing table

Question

EDIT: Is there a way to check if the constraint already exists to determine if it needs to be removed first?

I build a series of tables, and in two columns I set the default value to 'True' when new records are added.

It seems the constraint will be added, with the line of code noted below, but it does not create the default value of 'True' in the binding.

ISSUE:

I am trying to change the default value for an existing column in SQL Express 2008

ALTER TABLE [dbo].[tblLangtrTR] ADD  CONSTRAINT [DF_tblLangtrTR_displayRecord_1]  DEFAULT (N'True') FOR [displayRecord]

using the above code I get this error....

Msg 2714, Level 16, State 5, Line 2 There is already an object named 'DF_tblLangtrTR_displayRecord_1' in the database. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.

score 3 · Accepted Answer · answered Feb 02 '12 at 14:57

IF EXISTS(SELECT *
          FROM   sys.default_constraints
          WHERE  name = 'DF_tblLangtrTR_displayRecord_1'
                 AND parent_object_id = object_id('dbo.tblLangtrTR'))
  ALTER TABLE dbo.tblLangtrTR DROP CONSTRAINT
  [DF_tblLangtrTR_displayRecord_1]

ALTER TABLE dbo.tblLangtrTR ADD CONSTRAINT [DF_tblLangtrTR_displayRecord_1]
DEFAULT (N'True') FOR [displayRecord]

Looking for correct sql syntax to add default values to existing table

1 Answers1