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

Question

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.

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

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
  

Answer 1

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".

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).

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.

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".

Answer 2

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

Marc