Search code examples
sqlsql-serverforeign-key-relationship

"The DELETE statement conflicted with the REFERENCE constraint" while there is no data in referenced table

I have two related tables:

[GameDataGroup] with PK

[Arena_GameData] with FK

I try to execute query:

 DELETE FROM [ACP_MAIN_STABLE_DB_content].[dbo].[GameDataGroup] 
 WHERE [key] LIKE '%' + '_test_group' + '%'

And have a message:

The DELETE statement conflicted with the REFERENCE constraint "FK__Arena_GameData__GameDataGroup". The conflict occurred in database "ACP_MAIN_STABLE_DB_content", table "dbo.Arena_GameData", column 'gameDataGroupId'.

While there is no related data in "dbo.Arena_GameData", column 'gameDataGroupId'. Why it is prventing me to delete that record?

The 'FK__Arena_GameData__GameDataGroup' definition:

enter image description here enter image description here

Solution

  • You were getting more results than you expected with your DELETE query because _ is a wildcard character as far as LIKE is concerned.

    So LIKE '%_test_group%' will match any text which has at least one character, then the characters test, then any character, and then the characters group, Optionally followed by any number of characters - unlike what you probably expected of it needing to find exactly the sequence _test_group.

    You can use escaping if you need to do these sorts of matches - LIKE '%!_test!_group%' ESCAPE '!' should do what you're looking for.