add CHECK constraint to already populated table

Question

I created a table called test with column called code:

create table test(  
code char(3) not null);

I then populated the table with the following data:

insert into test values ('A12');
insert into test values ('B23');
insert into test values ('C45');

I then altered the column to make it char(4):

alter table test
alter column code char(4) not null;

I then added a 'X' to all existing data so that it becomes 4 characters long:

update test
  set code='X'+code
where LEN(code)=3;

So far so good but then when I tried to add a check constraint:

alter table test
add constraint codeCheck check (code like 'A-Z''A-Z''0-9''0-9');

I got this error:

The ALTER TABLE statement conflicted with the CHECK constraint "codeCheck".

I understand that the error implies that the existing data violates the check constraint that I am trying to add into the table, but why?

and how do I do it such that the existing data and check constraint do not violate each other?

Intellectual

Please use the { } button, not HTML markup, to format your post. – Inferential

Answer 1

Your pattern syntax is wrong. It should be

alter table test
add constraint codeCheck check (code like '[A-Z][A-Z][0-9][0-9]');

Tingle

Answer 2

Because your data doesn't match the like constraint.

Try

alter table test
     add constraint codeCheck check (code like '[A-Z][A-Z][0-9][0-9]' );

Renita

Answer 3

I don´t know how it works with SQL Server but your like clause looks odd. Try using

'[A-Z]{2}\d{2}'

Aggregate