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

Question

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:

Raffarty

Do you have trigger on ACP_MAIN_STABLE_DB_content? – Kingmaker

Thanks, how to check if i have trigger? Sorry i'am very new at sql – Raffarty

It seems i found place, 'Database Triggers", and there i can't see any triggers – Raffarty

Could you include FK__Arena_GameData__GameDataGroup defintion? – Kingmaker

added FK definition screenshots – Raffarty

Are you sure there is no related data? – Kingmaker

Possible expression LIKE '%' + '_test_group' + '%' was the issue, i replace it with = '_test_group' and problem is gone – Raffarty

You can put this solution as an answer – Kingmaker

Answer 1

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.

Underplay

Answer 2

You are getting an error message while deleting records from the table if any of the records have been referenced by any other tables, so you can not delete those records from the table which are having referenced by other or you should use cascade option to remove those reference as well.

Finable