Forum Discussion

shaam_'s avatar
shaam_
Copper Contributor
Apr 09, 2024

Unable to add CHECK CONSTRAINT

Hi, Community. The SQL server is new to me. I am still learning. I needed to remove a user from the database, when I tried to remove it I got the below error.

 

Msg 547, Level 16, State 0, Line 3 The DELETE statement conflicted with the REFERENCE constraint "fk_user_attribute_id_user_id". The conflict occurred in database "database_prod", table "dbo.dba_user_attribute", column 'user_id'.

 

Then I ran the below query to alter table and was able to delete the user. 

ALTER TABLE [database_prod].[dbo].[dba_user_attribute] NOCHECK CONSTRAINT [fk_user_attribute_id_user_id]

Now I am trying to add check constraint back by running the below query.

ALTER TABLE [database_prod].[dbo].[dba_user_attribute]  WITH CHECK CHECK CONSTRAINT [fk_user_attribute_id_user_id]

However I am getting the below error. Any idea how can add it back that record won't be delete it as it was before?

Msg 547, Level 16, State 0, Line 7
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_user_attribute_id_user_id". The conflict occurred in database "database_prod", table "dbo.dba_user", column 'id'.

Completion time: 2024-04-09T16:22:36.2757563-07:00

 

  • SivertSolem's avatar
    SivertSolem
    Iron Contributor
    Apr 12, 2024

    shaam_ 

    You have removed a row from one of the tables containing user information.
    "dbo.dba_user"
    "dbo.dba_user_attribute"

     

    There's a "FOREIGN KEY" constraint that you removed. This constraint ensured that all entries in one table maps to entries in the other table.

     

    Your 'ALTER TABLE [...] WITH CHECK CHECK CONSTRAINT [...]' fails because there's now a row in one of the tables that no longer map to the other.
    This query should provide you with the offending row.

     

    /*user attribute without user*/
SELECT dua.user_id FROM dbo.dba_user_attribute dua 
EXCEPT
SELECT du.id FROM dbo.dba_user
GO
/*user withouth user attribute*/
SELECT du.id FROM dbo.dba_user 
EXCEPT
SELECT dua.user_id FROM dbo.dba_user_attribute dua

     

    You should delete the rows from the secondary table that no longer match your primary table, to ensure data consistency.

     

    Or you can disregard data consistency entirely, and use 'ALTER TABLE [...] WITH NOCHECK CHECK CONSTRAINT [...]' instead. This is NOT recommended, as it can lead to other issues down the line.

Resources