Exclusion of records in cascade tables

Asked

Viewed 2,032 times

1

I have a "Terms" table that your ID is FK from "Thermosexpresso".

As shown in the figure below.

When I run the command "delete Terms Where Idtermo = 4" sql generates me

the following error.

The DELETE statement conflicted with the REFERENCE Constraint "Fk_thermoexpression". The Conflict occurred in database "Sprintjuncao", table "dbo.Termoexpressao", column 'Idtermoexpressao'. The statement has been terminated.

From what I read it seems that this is a cascade deletion, when I delete the record of "Terms" I should automatically delete the records related to "Thermosexpresso", In the case of all records in Thermosexpression with Idtermo = 4

Can anyone help me how I should proceed in this case?

inserir a descrição da imagem aqui

sql

  • you will have to delete the data from the table first TermoExpressao, you can do it like this delete dbo.TermoExpressao where IDTermo = 4

  • Another thing you could do would be change your FK to be delete cascade

  • @Pablovargas I think the correct in my case would be this delete?

1 answer

2


The best solution then is to change your FK so that it is delete cascade

ALTER TABLE [dbo].[TermoExpressao] DROP CONSTRAINT [FK_TermoExpressao_Termos1]
GO
ALTER TABLE [dbo].[TermoExpressao]  WITH CHECK ADD  CONSTRAINT [FK_TermoExpressao_Termos1] FOREIGN KEY([IDTermo])
REFERENCES [dbo].[Termo] ([IDTermo])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TermoExpressao] CHECK CONSTRAINT [FK_TermoExpressao_Termos1]
GO

another option to resolve this would be to modify your FK as per the images.

Modificar FK

Delete cascade

  • a Fk_termosexpressao_terms is already this way. It has another Fk_termosexpressao_termos1 that references Idtermosexpressao to Idtermos. Without this, is the error being generated there?

  • You’ll probably have to put both your FK’s delete cascade

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.