DROP CONSTRAINT

  • I'm trying to drop a constraint on a table so I can then drop a column.

    The constraint is of type PRIMARY KEY (clustered) according to sp_help. 

    I want to write a query, that drops the constraint, drops the column, then re-adds the constraint.

    How does this look?

    DECLARE @Err AS INT

    BEGIN TRANSACTION

    ALTER TABLE dmvrequest

    DROP CONSTRAINT PK_dmvrequest

    GO

    ALTER TABLE dmvrequest

    DROP claimraw

    GO

    ALTER TABLE dmvrequest

    ADD CONSTRAINT PK_dmvrequest 

    Select @Err = @@ERROR

    if @Err <> 0 Goto QuitWithRollBack

    COMMIT TRANSACTION

    Return 0

    QuitWithRollBack:

     ROLLBACK TRAN

     Return @Err

  • the add constraint part is not complete.. you need to define the constraint in details. Check bols for the correct syntax.

  • I found how to do it with the Enterprise Manager, but can't find any reference in BOLS to do it through T-SQL

     

    THANKS

     

    Magy

     

     

  • Do it again in EM. But before saving it, click on the script button to see how EM does it. Then you'll know what to search for. Also keep in mind that EM often does a lot of steps that could be done in more efficient ways. This is for backward compatibility.

  • Here's the best I could find...but after looking at it, I'm not sure if this is even possible without ruining the table.

     

    ALTER TABLE table

    { [ ALTER COLUMN column_name

        { new_data_type [ ( precision [ , scale ] ) ]

            [ COLLATE < collation_name > ]

            [ NULL | NOT NULL ]

            | {ADD | DROP } ROWGUIDCOL }

        ]

        | ADD

            { [ < column_definition > ]

            |  column_name AS computed_column_expression

            } [ ,...n ]

        | [ WITH CHECK | WITH NOCHECK ] ADD

            { < table_constraint > } [ ,...n ]

        | DROP

            { [ CONSTRAINT ] constraint_name

                | COLUMN column } [ ,...n ]

        | { [ WITH CHECK | WITH NOCHECK ] CHECK | NOCHECK } CONSTRAINT

            { ALL | constraint_name [ ,...n ] }

        | { ENABLE | DISABLE } TRIGGER

            { ALL | trigger_name [ ,...n ] }

    }

  • Back to square one. What do you want to do and why?

  • I'm trying to drop a column, but I get that error:

    Server: Msg 3728, Level 16, State 1, Line 1

    'claimraw' is not a constraint.

    Server: Msg 3727, Level 16, State 1, Line 1

    Could not drop constraint. See previous errors.

    So I was trying to write a transaction that dropped the constraint, dropped the unwanted column, then re-adds the constraint. 

     

  • You can't add a constraint on a non-existing column???

    Also have you tried "drop column clainraw"

  • I'm totally confused.

    You should not have to drop the primary key constraint on a table just to drop a column. (Unless the column you want to drop participates in the primary key) but have not stated that.

    Take the following as an example

    use pubs

    -- Create table with Primary Key

    create table Testtable (pk int identity primary Key, foo int, bar char(10))

    go

    sp_help TestTable

    go

    -- Drop one of the columns do not need to drop PrimaryKey Constraint

    alter table Testtable drop column bar

    go

    sp_help TestTable

    go

    drop table TestTable

     

    I think you may have an error here:

    ALTER TABLE dmvrequest

    DROP claimraw

    It should be

    ALTER TABLE dmvrequest

    DROP COLUMN claimraw

    Please post the create table statement for the way the table currently is, and someone can possibly show you the answer.

    also your add constraint syntax is not correct

    it should be

    ALTER TABLE dmvrequest

    ADD CONSTRAINT ConstraintName

    PRIMARY KEY CLUSTERED

    (

      pk_

    )

  • Ray:

     

    I'm such an idiot.  You were right, I forgot the COLUMN keyword in my DROP statement.  Once I added that, it worked beautifully!

     

    Thanks!

     

  • It's a good thing that my name is also ray because I posted the exact same comment 20 mins before him.

Viewing 11 posts - 1 through 10 (of 10 total)

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