Post reply

How To Delete a Default Constraint

  • July 23, 2003 at 4:42 pm

    #64967

    I wonder if someone could tell me how to drop the default constraint on a certain column in a table.

    Thank you very much for your help.

  • July 23, 2003 at 11:54 pm

    #466703

    Hi wmp789,

    quote:

    I wonder if someone could tell me how to drop the default constraint on a certain column in a table.

    sp_helpconstraint 'table_name' will give you the name(s) of all constraints on the table in question.

    ALTER TABLE table_name

    DROP CONSTRAINT constrain_name

    GO

    should remove it

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 24, 2003 at 10:02 am

    #466867

    Thank you, Frank, for your help.

    I ran 'sp_helpconstraint'. It listed three Default constraints on three different columns. I only need to drop the Default constraint on one column. How should I put the DROP CONSTRAINT statement? Thanks again.

  • July 24, 2003 at 10:10 am

    #466870

    Should I use DROP DEFAULT or DROP CONSTRAINT?

    What is the difference? Thank you.

  • July 24, 2003 at 10:51 am

    #466881

    I dropped the constraint successfully 🙂

    Thank you, Frank, for your help.

    I am still confused about DROP DEFAULT...

  • July 25, 2003 at 12:19 am

    #466971

    Hi wmp789,

    quote:

    I dropped the constraint successfully 🙂

    Thank you, Frank, for your help.

    I am still confused about DROP DEFAULT...

    from my understanding, you can create a DEFAULT that is available database-wide. You then can bind this default to as many columns in as many tables in this db as you like, unless they are of compatible data types.

    BOL has a nice example on this. See CREATE DEFAULT, DROP DEFAULT and/or sp_bindefault for further informations.

    A DEFAULT CONSTRAINT is only valid on one column in one table.

    HTH.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 25, 2003 at 11:48 am

    #467125

    Thank you very much for your help, Frank.

Viewing 7 posts - 1 through 6 (of 6 total)

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