Mercer Mercer - 7 months ago 12
SQL Question

Delete specific values from column

I want to delete specific values/data from one column with the

WHERE
condition.

Table
CIVILITE
:

ID_CIVILITE CIV_LIBELLE
1 M.
2 Mme
3 Mlle
4 Aucun

DELETE FROM CIVILITE WHERE CIV_LIBELLE='Aucun';


Error:


The DELETE statement is in conflict with the constraint REFERENCE
"FK_PERS_CIVILITE". The conflict occurred in database "DBDB",
"dbo.PERSONNE" table, column 'ID_CIVILITE'.


How can delete some value where there is a constraint ..?

Answer

The reason why you are getting this error is because you are trying to delete a row which is being referenced by another row hence resulting in the error. So either delete the reference row or remove the constraint temporarily. You need to first alter your table like this:

ALTER TABLE [DBDB].[dbo].[PERSONNE] NOCHECK CONSTRAINT [FK_PERS_CIVILITE]

and then you can delete the record.

Make sure that once you delete the record you apply the constraint again.

ALTER TABLE [DBDB].[dbo].[PERSONNE] WITH CHECK CHECK CONSTRAINT [FK_PERS_CIVILITE]