CODEWITHSUNDEEP
sqlsql-serverforeign-key-relationship
igorGIS
igorGIS

Reputation: 1946

"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

Upvotes: 0

Views: 2156

Answers (2)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

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.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

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.

Upvotes: 1

Related Questions