Sql 服务器错误：INSERT 语句与 CHECK 约束冲突

Sql server error : The INSERT statement conflicted with the CHECK constraint

提问by Anuya

The INSERT statement conflicted with the CHECK constraint "ck_str_member_no". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\KARTHIKEYAN\DESKTOP\KOK\DB\INFT3009_ASS1_C3104855.MDF", table "dbo.Members", column 'str_member_no'. The statement has been terminated.

INSERT 语句与 CHECK 约束“ ck_str_member_no”冲突。冲突发生在数据库“C:\DOCUMENTS AND SETTINGS\KARTHIKEYAN\DESKTOP\KOK\DB\INFT3009_ASS1_C3104855.MDF”，表“dbo.Members”，列“ str_member_no”中。该语句已终止。

I am using .MDF file in my visual studio 2008 Express. How do I solve it?

我在 Visual Studio 2008 Express 中使用 .MDF 文件。我该如何解决？

My insert Procedure :

我的插入程序：

---

---

ALTER PROCEDURE [dbo].[AddNewAGCMember] 
  -- Add the parameters for the stored procedure here
  @str_member_no varchar(6) = '', 
  @str_member_name varchar(50) = '',
  @str_member_password varchar(10) = '',    
  @str_addr_apartment_no varchar(10) = NULL,    
  @str_addr_building_name varchar(50) = NULL,   
  @str_addr_street_name varchar(50) = NULL, 
  @int_postal_code int = NULL,  
  @str_country_name varchar(50) = NULL, 
  @int_contact_no int = NULL,   
  @str_email_addr varchar(100) = '',    
  @date_registration date = ''  
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  INSERT INTO Members 
    (str_member_no,
     str_member_name,
     str_member_password,
     str_addr_apartment_no,
     str_addr_building_name,
     str_addr_street_name,
     int_postal_code,
     str_country_name,
     int_contact_no,
     str_email_addr,
     date_registration) 
  VALUES
    (@str_member_no, 
     @str_member_name,
     @str_member_password,
     @str_addr_apartment_no,
     @str_addr_building_name,
     @str_addr_street_name,
     @int_postal_code,
     @str_country_name,
     @int_contact_no,
     @str_email_addr,
     @date_registration);
END

---

---

Table structure :

表结构：

• str_member_no, varchar(6), Unchecked
• str_member_name, varchar(50), Unchecked
• str_member_password, varchar(10), Unchecked
• str_addr_apartment_no, varchar(10), Checked
• str_addr_building_name, varchar(50), Checked
• str_addr_street_name, varchar(50), Checked
• int_postal_code, int, Checked
• str_country_name, varchar(50), Checked
• int_contact_no, int, Checked
• str_email_addr, varchar(100), Unchecked

• date_registration, date, Unchecked

  Unchecked
  

• str_member_no, varchar(6), 未选中
• str_member_name, varchar(50), 未选中
• str_member_password, varchar(10), 未选中
• str_addr_apartment_no, varchar(10), 检查
• str_addr_building_name, varchar(50), 检查
• str_addr_street_name, varchar(50), 检查
• int_postal_code, int, 检查
• str_country_name, varchar(50), 检查
• int_contact_no, int, 检查
• str_email_addr, varchar(100), 未选中

• date_registration, 日期, 未选中

  Unchecked
  

回答by marc_s

Obviously, your insert is violating a check constraint. This is a constraint in your database the performs a specific check - that a numeric value is in a particular range, that a string is at most n characters long, or whatever.

显然，您的插入违反了检查约束。这是您的数据库中的一个约束，它执行特定的检查 - 数值是否在特定范围内，字符串最多为 n 个字符，或其他任何内容。

To find out what the check constraint is, try this:

要找出检查约束是什么，请尝试以下操作：

SELECT
name, definition 
FROM
    sys.check_constraints
WHERE
    name = 'ck_str_member_no'

This will give you the expression that is being checked in the "definition" column.

这将为您提供在“定义”列中检查的表达式。

From that expression, you should be able to determine why your insert is being rejected. Fix the problem and insert again.

从该表达式中，您应该能够确定插入被拒绝的原因。解决问题并再次插入。

If you really cannot fix your data, and if you do not need / want that check constraint in place, you can drop it:

如果您确实无法修复数据，并且不需要/不希望该检查约束到位，则可以删除它：

ALTER TABLE dbo.Members
   DROP CONSTRAINT ck_str_member_no

Marc

马克

回答by mjv

Apparently the INSERT is supplying a value for column 'str_member_no' which SQL has been instructed to refuse given the CHECK constraint. A check constraint is simply a condition imposed on one or several values of fields, before they can be added or inserted. For example, maybe the CHECK requires that all str_member_no be more than 1500 (the lower values being used for, say, administators...) or some other odd "business rule".

显然，INSERT 正在为列 'str_member_no' 提供一个值，在给定 CHECK 约束的情况下，SQL 已被指示拒绝该值。检查约束只是在添加或插入字段之前强加于一个或多个字段值的条件。例如，CHECK 可能要求所有 str_member_no 大于 1500（较低的值用于，例如，管理员...）或其他一些奇怪的“业务规则”。

By inspecting, or posting here, the DDL for the underlying table creation, as well as the INSERT statement in question, you could get specific understanding of what is wrong with the value (or with the check predicate).

通过检查或在此处发布基础表创建的 DDL 以及有问题的 INSERT 语句，您可以具体了解值（或检查谓词）有什么问题。

Reading the added code in the question, the str_member_no doesn't seem to be checked, then it may just be that the value passed is too long or null or some other generic reason for refusing it; the error would typically be different but since there is a check, the error shown is based on the assumption that the [custom] check failed.

阅读问题中添加的代码，似乎没有检查str_member_no，那么可能只是传递的值太长或为空或其他一些拒绝它的一般原因；错误通常会有所不同，但由于存在检查，因此显示的错误是基于 [custom] 检查失败的假设。

At any rate, you should check what values are passed to the AddNewAGCMember() StoredProcedure; the problem probably lies there, in a field value that is "bad".

无论如何，您应该检查传递给 AddNewAGCMember() StoredProcedure 的值；问题可能就在那里，在一个“坏”的字段值中。

回答by Rajesh Paul

Check the following MySQL bug that has not yet been solved-

检查以下尚未解决的MySQL错误-

MySQL CHECK constraint bug

MySQL CHECK 约束错误