September 12, 2005 at 1:29 pm
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
September 12, 2005 at 1:32 pm
the add constraint part is not complete.. you need to define the constraint in details. Check bols for the correct syntax.
September 12, 2005 at 1:38 pm
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
September 12, 2005 at 1:42 pm
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.
September 12, 2005 at 1:53 pm
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 ] }
}
September 12, 2005 at 1:55 pm
Back to square one. What do you want to do and why?
September 12, 2005 at 2:03 pm
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.
September 12, 2005 at 2:13 pm
You can't add a constraint on a non-existing column???
Also have you tried "drop column clainraw"
September 12, 2005 at 2:36 pm
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_
)
September 13, 2005 at 6:03 am
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!
September 13, 2005 at 6:50 am
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