-1

I tried to import a record with a semicolon in the addr field. Once I removed the semicolon from the import file, the following error went away:

Database error occurred SQLSATE = 23000
[Microsoft][ODBC SQL Server Driver][SQL Server]
The INSERT statement conflicted with the CHECK constraint "addr_customer". The conflict occurred in database "db",table "dbo.customer", column 'addr'.

The stored procedure has this passage. I'm trying to understand in the LIKE clause, what other characters the constraint check is looking for. Obviously semicolon is disallowed. Do I read the clause where each character listed between '%[ and ']% are the disallowed characters (e.g. " | ; % _ ' )?

TABLE [dbo].[customer]  WITH 
CHECK ADD  CONSTRAINT 
[addr_customer] 
CHECK  ((NOT [addr] like '%[,"|;%_'']%'))
0

1 Answer 1

5

This CHECK constraint verifies that the value does not match the LIKE pattern.

The pattern is evaluated as follows

  • % start with any, multiple or no characters
  • [,"|;%_''] any of the actual characters within the brackets (not including the brackets), and the ' is doubled due to escaping.
  • % followed with any, multiple or no characters

So essentially, the value cannot contain any of the characters ,"|;%_'.

It also allows NULLs to be inserted, assuming there is not also a NOT NULL constraint.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.