Post reply

How to disable a default constraint in Sql Server 2005?

  • September 3, 2010 at 2:33 pm

    #95070

    Hi,

    I'm trying to disable a defualt constraint on one of my table.I know that we can drop and recreate the DF_Copnstraint..but I want to know is there any way to disable the Default constraint?

    Thx in Advance

    Pavan Posani

  • September 3, 2010 at 4:05 pm

    #1216445

    According to BOL, you cannot disable a DEFAULT constraint. You CAN disable a FOREIGN KEY or CHECK constraint.

    { CHECK | NOCHECK } CONSTRAINT

    Specifies that constraint_name is enabled or disabled. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

    You'll have to DROP and later recreate the constraint.

    Edit: This is the case for SQL 2005 at least. I don't believe that's changed for SQL 2008

    Rob Schripsema
    Propack, Inc.

  • September 3, 2010 at 4:15 pm

    #1216447

    also notehow a DEFAULT constraint is used! default does not mean "replace null with the default value", it means "if not included, use THIS"

    if you include a value in your update statement for the columns with a default, no default is used.

    only when you insert data, and your insert statement does not reference the column with the default constraint, is the default value used.

    so if your db has a column CreatedDate with a default value of getdate, just include NULL or whatever value you want in your statement....simple.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply