CODEWITHSUNDEEP
sqlsql-server
niru dyogi
niru dyogi

Reputation: 639

Dropping and adding a constraint in sql

I want to drop and add a constraint

ALTER TABLE [dbo].[Entity] DROP  CONSTRAINT [x_UpdateDate1]  
-- default value 
ALTER TABLE [dbo].[Entity] ADD CONSTRAINT [x_UpdateDate1]  
                           DEFAULT ([dbo].[GETSYSTEMDATE]()) 
                           FOR [CreateDate]

I want to find the alternative for this. As dropping and adding a constraint is not advisable. Is there any better way to handle

Upvotes: 0

Views: 2624

Answers (3)

Gabriel M
Gabriel M

Reputation: 704

No, there's no other way than to drop and re-create constraints (check also sqlauthority.com blog entry)

Upvotes: 1

RosSQL
RosSQL

Reputation: 323

You can disable constraints with: 

ALTER TABLE tbl NOCHECK CONSTRAINT all

and then turn them back on with:

ALTER TABLE tbl WITH CHECK CHECK CONSTRAINT all

of course this SQL turns off ALL constraints on the table but you can specify an individual constraint like this:

ALTER TABLE tbl NOCHECK CONSTRAINT Constraint1

Upvotes: -2

peter.petrov
peter.petrov

Reputation: 39457

No. You cannot alter a constraint, you need to drop it and then recreate it just as you did.

Upvotes: 6

Related Questions