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

2021-09-10 00:00:00 sql tsql sql-server

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

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.

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

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

我的插入程序:

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)，未选中
  • 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), Checked
  • int_postal_code, int, Checked
  • str_country_name, varchar(50), Checked
  • int_contact_no, int, Checked
  • str_email_addr，varchar(100)，未选中

  • date_registration，日期，未勾选

  • 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

推荐答案

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

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.

要了解检查约束是什么，请尝试以下操作:

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

马克

相关文章