views:

1497

answers:

2

This is the constraint I have on the Customers table.

ALTER TABLE Customers
    ADD CONSTRAINT CN_CustomerPhone
    CHECK (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

Why does this fail?

INSERT INTO Customers
(CustomerName, Address, City, State, Zip, Phone)
VALUES
('Some Name','An Address', 'City goes here', 'WI', 12345, '(800) 555-1212')

With this error message.

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CN_CustomerPhoneNo". The conflict occurred in database "Accounting", table "dbo.Customers", column 'Phone'. The statement has been terminated.

I'm sure I'm missing something really simple, but I can't find it.

I've tried simplifying the constraint to only 'Phone LIKE '[0-9]'' and inserting a single digit, but it still fails. WTF?

+2  A: 

Check the length of the Phone field.
Is it 15 or more characters?

Using your code with a temp table here


create table #temp
(phone varchar(15))

ALTER TABLE #temp
    ADD CONSTRAINT CN_CustomerPhone
    CHECK (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

INSERT INTO #temp
(Phone)
VALUES
('(800) 555-1212')

select * from #temp

drop table #temp

shahkalpesh
Yeah, it was a typo.
Eric Haskins
+1  A: 

I'm an idiot, the field was declared as an nchar, and I didn't' take the padding spaces into account.

Eric Haskins