Technical Article

Check constraint to prevent insertion of invalid characters.

,

A column level check constraint may be used to prevent insertion of data containing characters within a specific ASCII range. In the supplied example, we are preventing insertion of data containing character within the range of char(0) - char(31). For example, it will reject data containing embedded tabs {9}, carriage returns {13}, or line feeds {0}. It is reccomended that you provide the constraint with a descriptive name, because it will assist users with identifying the source of the error.
Here is the result of inserting data containing a trailing tab.
insert into MySample ( col1 )
  values ('John'+char(9));
Msg 547, Level 16, State 0, Line 20
The INSERT statement conflicted with the CHECK constraint "cc_col1_InvalidData". 
The conflict occurred in database "master", table "dbo.MySample", column 'col1'.
create table MySample
(
  col1 varchar(30) null 
    constraint cc_col1_InvalidData check 
    (case when col1 like '%['+char(0)+'-'+char(31)+']%' 
    then 0 
    else 1 
    end = 1)
);

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.33 (6)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.33 (6)

You rated this post out of 5. Change rating

Related content

External Article

Declarative SQL: Using UNIQUE Constraints

In SQL, you can express the logic of what you want to accomplish without spelling out the details of how the database should do it. Nowhere is this more powerful than in constraints. In this introduction to Declarative SQL, Joe Celko demonstrates how you can write portable code that performs well and executes some complex logic, merely by creating unique constraints.

2015-12-15

6,149 reads

External Article

Set-based Constraint Violation Reporting in SQL Server

When you're importing data into an RDBMS in bulk and an exception condition is raised because of a constraint violation, you generally need to fix the problem with the data and try again. The error won't tell you which rows are causing the violation. What if you've thousands of rows to search when it happens? There are solutions, writes William Sisson.

2014-10-16

8,821 reads