“DELETE语句与参考约束冲突”而引用表中没有数据 [英] "The DELETE statement conflicted with the REFERENCE constraint" while there is no data in referenced table

问题描述

我有两个相关的表格：

pre $ [code] [GameDataGroup]与PK

[Arena_GameData]与FK

我尝试执行查询：

 DELETE FROM [ACP_MAIN_STABLE_DB_content]。[dbo]。[GameDataGroup] 
 WHERE [key] LIKE'％'+'_test_group'+'％'
  
 
 $ b 
并且有一个消息：
 
lockquote 
 
 DELETE语句与参考约束
FK__Arena_GameData__GameDataGroup冲突。数据库
ACP_MAIN_STABLE_DB_content，表dbo.Arena_GameData，列
'gameDataGroupId'。
 
虽然dbo.Arena_GameData，列gameDataGroupId中没有相关数据。为什么它会阻止我删除该记录？ 
 
 
'FK__Arena_GameData__GameDataGroup'定义：
 
 
  
  
解决方案 
您获得的结果比您预想的要多 DELETE 查询，因为 _ 是通配符，只要  LIKE  有关。
 
 
 
  LIKE'％_test_group％'将匹配任何至少包含一个字符的文本，然后匹配字符 test ，然后字符，然后是字符 group ，可选地后跟任意数量的字符 - 不像你可能期望的那样，它需要精确地找到序列 _test_group 。
 
 
 
如果您需要进行这些类型的匹配，您可以使用转义 -   LIKE'％！_ test！_group％'ESCAPE'！'寻找。
 
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:


解决方案
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.

                        
这篇关于“DELETE语句与参考约束冲突”而引用表中没有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！