英文:
How can I resolve an INSERT statement conflict with a CHECK constraint in SQL?
问题
我遇到了这个错误：
> Msg 547，级别 16，状态 0，行 105
> INSERT 语句与 CHECK 约束 "CK__Orders__Order_St__33D4B598" 冲突。冲突发生在数据库 "Online_Sales"，表 "dbo.Orders"，列 'Order_Status'。
我尝试在 SQL Server 中向表中插入数据时遇到了这个错误。
英文:
I get this error:
> Msg 547, Level 16, State 0, Line 105
> The INSERT statement conflicted with the CHECK constraint "CK__Orders__Order_St__33D4B598". The conflict occurred in database "Online_Sales", table "dbo.Orders", column 'Order_Status'.
I tried inserting data into a table in SQL Server, when I got this error.
答案1
得分: 1
以下是翻译好的内容：
这个表上定义了一个检查约束，基本上是说：“除非你满足这些规则，否则无法插入数据。” 你需要确定这些规则（检查约束的定义）是什么，并确保你的数据能够通过这个测试。
在SQL Server Management Studio (SSMS) 中，有多种方法可以查找这些规则。另一种选择是查询系统表，这通常是我采用的方法之一，但仅因为我有多年积累的一堆支持脚本。下面是一个应该帮助你找到相关代码的示例查询：
SELECT
sc.name [模式（CKs）]
,taP.name [表]
,cc.name 检查约束名称
,CASE WHEN col.name IS NULL THEN '...' ELSE col.name END [列或表]
,cc.is_disabled
,cc.is_not_trusted
,cc.definition
FROM sys.check_constraints cc
INNER JOIN sys.tables taP
ON taP.object_id = cc.parent_object_id
INNER JOIN sys.schemas sc
ON sc.schema_id = taP.schema_id
LEFT OUTER JOIN sys.columns col
ON col.object_id = taP.object_id
AND col.column_id = cc.parent_column_id
--WHERE sc.name = '??'
-- AND taP.Name = '??'
请注意，这是一个SQL查询，用于检索检查约束的信息。
英文:
There is a check constraint defined on the table that, essentially, says "you can't insert data unless you pass these rules." You need to determine what those rules (the check constraint's definition) are, and make sure your data can pass that test.
There are ways to drill down in SSMS to find these rules. Another alternative is to query the system tables, which is what I generally do--but only because I have access to a bunch of support scripts, accumulated over the years. Below is one that should help you find the code in question.
SELECT
sc.name [Schema (CKs)]
,taP.name [Table]
,cc.name CheckName
,case when col.name is null then '...' else col.name end [Col or Tbl]
,cc.is_disabled
,cc.is_not_trusted
,cc.definition
from sys.check_constraints cc
inner join sys.tables taP
on taP.object_id = cc.parent_object_id
inner join sys.schemas sc
on sc.schema_id = taP.schema_id
left outer join sys.columns col
on col.object_id = taP.object_id
and col.column_id = cc.parent_column_id
--where sc.name = '??'
-- and taP.Name = '??'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论