none
CONSTRAINT check

    Question

  • Tools: Microsoft management tools 2008 R2

    Environment: Windows 7 and SQL server 2008

    Goal: add CONSTRAINT check on ID column to verify whether the LEN(ID) is 9. Knowing that ID sometimes contains ' ' if there is no value. ID is not a primary key nor foreign key in table A

    Problem: When I added CONSTRAINT check on ID column, an error message appeared:  ALTER TABLE statement conflicted with the CHECK constraint.

    ADD  CONSTRAINT [DF_tbl_b]  DEFAULT ('') FOR [ID]

    what I could do different to fulfill my goal and to avoid the check conflict error? 

    I tried the following code:

    ALTER Table dbo.tbl_A_I 
    ADD CONSTRAINT CK_tbl_a_I
    CHECK (LEN(ID)=9 or LEN(ID)=1)

    This code conflict with the following code. I also tried to delete the code and it shows an error message "the default "DF_tbl_A" does not exist on the server SqlManagerUI"

    ALTER TABLE [dbo].[tbl_A] ADD  CONSTRAINT [DF_tbl_A]  DEFAULT ('') FOR [ID]


    but no use.


    • Edited by Sandra VO Monday, November 26, 2012 7:43 PM
    Monday, November 26, 2012 7:09 PM

Answers

  • ALTER TABLE statement conflicted with the CHECK constraint.

    ....

    what I could do different to fulfill my goal and to avoid the check conflict error? 

    Hello,

    Have you checked your existing data if they fullfill this new constraint? Otherwise you should update your data to fullfil the constraint.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Naomi NModerator Monday, November 26, 2012 7:50 PM
    • Marked as answer by Sandra VO Monday, November 26, 2012 10:59 PM
    Monday, November 26, 2012 7:16 PM
  • Yes, exactly. You first need to correct bad values and then you will be able to add the constraint.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 8:20 PM
    Moderator

All replies

  • ALTER TABLE statement conflicted with the CHECK constraint.

    ....

    what I could do different to fulfill my goal and to avoid the check conflict error? 

    Hello,

    Have you checked your existing data if they fullfill this new constraint? Otherwise you should update your data to fullfil the constraint.


    Olaf Helper

    Blog Xing

    • Proposed as answer by Naomi NModerator Monday, November 26, 2012 7:50 PM
    • Marked as answer by Sandra VO Monday, November 26, 2012 10:59 PM
    Monday, November 26, 2012 7:16 PM
  • Somehow you are contradicting yourself by using an empty string as a default and also checking that the LEN should be 1 or 9. The LEN of the empty string or an string with just spaces will be zero.

    select len('') as c1, len('     ') as c2;
    GO

    Try to identify first existing rows not passing this check.

    select ID from T where LEN(ID) not in (1, 9); -- supposing ID is doesn't allow NULL mark


    AMB

    Some guidelines for posting questions...

    Monday, November 26, 2012 7:43 PM
    Moderator
  • @ Olaf Helper: I did not get your point. 

    @ Hunchback: I run the command lime to identify first existing rows not passing this check.and I found approx 200 records.

    • Edited by Sandra VO Monday, November 26, 2012 7:58 PM
    Monday, November 26, 2012 7:43 PM
  • Hello Sandra,

    If already data exists in the table, which don't fullfill the new constraint = Len <> 9 and Len <> 1, then you can't create the constraint. So check your data for that condition.


    Olaf Helper

    Blog Xing

    Monday, November 26, 2012 7:49 PM
  • You should change the constraint to

    LEN(ID) = 9 or LEN(ID) = 0 (to allow empty values).

    You should also make sure that you don't have bad data already, e.g.

    select * from myTable where NOT LEN(ID) IN (0,9)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 7:54 PM
    Moderator
  • @Naomi N: are you saying that in order to fulfill my goal, I have to first clean up the data and then add constraint  check (LEN(ID) = 9 or LEN(ID) = 0).?
    Monday, November 26, 2012 8:13 PM
  • Yes, exactly. You first need to correct bad values and then you will be able to add the constraint.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 8:20 PM
    Moderator
  • >> I run the command line to identify first existing rows not passing this check and I found approx 200 records [sic]. <<

    Rows are not records; among the many differences is that rows can have constraints. Tables are sets, so there is no ordering; the concept "first rows" is absurd in SQL.

    You have to write an UPDATE statement to correct the bad data.  Once the column is corrected, then add the new constraint.

    I would suggest that you re-think the design of this vague, generic "id" while you have a chance.  Change the name to "<something>_id"  as per ISO-11189 rules. Make it fixed length and create an explicit  code for missing or unknown values. All zeros is generally used for "missing or unknown"  and all nines for "miscellaneous" values. If possible used a regular expression for the column instead of inviting garbage data.  For example the ISO sex codes follow this pattern in just one column! 

     sex_code CHAR(1) DEFAULT '0' NOT NULL CHECK (sex_code IN ('0', '1', '2', '9'))

    0= unknown, 1=male, 2=female, 9=lawful person (corporation, organisation, etc)

       


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, November 26, 2012 11:50 PM