Dropping and recreating Constraints in SQL Server

问题:

I am doing the below SQL operation

insert Component values (201, 19, 1, 'Statements', 1, 10)

I have to revert it back using a script which I do as below

delete from Component where ComponentID = 201 and ComponentTypeID = 19

The issue is I am getting a error as below

The DELETE statement conflicted with the REFERENCE constraint "FK_Component_ComponentType". The conflict occurred in database "Test", table "Component", column 'ComponentTypeID'.

So what I am doing to solve this issue is dropping the constraint and adding it back when I am done deleting the row like below

alter table Component   drop
        FK_Component_ComponentType

and then adding it back

ALTER TABLE [dbo].[Component]  WITH CHECK ADD  CONSTRAINT [FK_Component_ComponentType] FOREIGN KEY([ComponentTypeID])
REFERENCES [dbo].[ComponentType] ([ComponentTypeID])
ALTER TABLE [dbo].[Component] CHECK CONSTRAINT [FK_Component_ComponentType]

My question is that is this the right way to do it and is there any harm in dropping and recreating the constraint? Will there be any data loss or any other issue in this operation?

回答1:

You really shouldn't need to drop and re-create constraints like that unless you're actually getting rid of a foreign key value. It looks like you have another FK constraint on

Element.ComponentType REFERENCES Component.ComponentType

Check your foreign keys in dbo.Element and see if you need to drop/alter it there.