Dropping and recreating Constraints in SQL Server

Question

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?

Why is it the error says the constraint is on the table Element, and yet you say you are fixing it by dropping a constraint on Component? It doesn't make sense to me that the action you say you are taking is solving the problem you say you are having. — Tab Alleman
– Tab Alleman, Commented Aug 29, 2017 at 18:25
Sorry Element is not there had it by mistake..Fixed the question — SP1
– SP1, Commented Aug 29, 2017 at 18:29
You must have a relationship, either in this table, or another, that is relying on ComponentTypeID. Your question does not provide enough information to tell. And, if your relationship is on another table, my guess is that the relationship is backward. You should be able to delete rows in Component that have a fk to ComponentType, which is how I would think the relationship would work. And, by the way, if you are doing an insert and an immediate delete, you can always wrap everything in a transaction in t-sql and roll it back. so, begin trans...insert....select for testing....rollback. — Ron
– Ron, Commented Aug 29, 2017 at 18:37

Russell Fox · Accepted Answer · 2017-08-29 18:34:35Z

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.

Collectives™ on Stack Overflow

Dropping and recreating Constraints in SQL Server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related