gary.morey avatar image
gary.morey asked

CHECK constraint issue

SQL Server 2008 R2. I’m getting an exception due to a violated table constraint (this is in the [ecs_subpopulaterm_Public_09_2013] table): INSERT statement conflicted with the CHECK constraint "CK_ecs_subpo_M1_RE_26A5A30" The constraint is on valid values: [M1_RES_PHN_CD]='G' OR [M1_RES_PHN_CD]='B' OR [M1_RES_PHN_CD]='*' Yet the data set being imported has values outside of the constraint. SELECT M1_RES_PHN_CD FROM [ecs_subpopulaterm_Public_09_2013] GROUP BY M1_RES_PHN_CD M1_RES_PHN_CD G B * M1_RES_PHN_CD is defined as varchar(10) and allows NULLS in the table. I queried the table for WHERE M1_RES_PHN_CD IS NULL and it returned no rows. Do you have any thoughts as to why the CHECK constraint returned an error, but appears to have allowed blanks (spaces) to be inserted (first line under column heading M1_RES_PHN_CD in result set)?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Your question is twofold if I understand it: - Why did the CHECK constraint cause an error with an insert? - How can there be rows in the table not fullfilling the CHECK constraint condition? First question: The only reason I can think of is that you are trying to import data which does not fullfill the CHECK condition of the constraint. You need to find out which row from the source data causes the check condition to not be fullfilled. Second question: If you already have data in your table, and add a CHECK constraint, using WITH NOCHECK, you can have whatever data you want in the table, the constraint will only validate new or updated rows.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gary.morey avatar image gary.morey commented ·
I am actually trying to assist someone else on this. He was trying to import data into the table and that is when he received the constraint violation error. However, the table was empty prior to doing the import. As mentioned previously the constraint definition is as follows: [M1_RES_PHN_CD]='G' OR [M1_RES_PHN_CD]='B' OR [M1_RES_PHN_CD]='*' When querying the table for distinct M1_RES_PHN_CD values, it returned G, B, * and spaces (not NULLS as I queried the table for records where the M1_RES_PHN_CD values were NULL and it returned no rows). I understand that a CHECK constraint evaluates to False when the definition for the constraint is not met for the rows. I also understand that NULL is evaluated as UNKNOWN by the constraint and those rows may be imported. However, I would have thought that values of blank (spaces) would have evaluated to False and not imported. There are over 100K of these rows in the table. In short, my question would be 'do you have a thought as to why those rows with spaces in the M1_RES_PHN_CD column would not have been caught by the constraint and were imported into the table?'
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Hi again. If the data was bulked into the table, with the IGNORE_CONSTRAINTS hint (default for BCP and BULK INSERT), the constraint won't have been checked.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.