sqlsql-servert-sql

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

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:

Solution

  • 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