Q

How to create an SQL CHECK constraint for two letters

SQL expert Rudy Limeback explains how to create a SQL CHECK constraint for two letters when trying to write a query to retrieve data from two tables.

I am using a check constraint to a variable with a VARCHAR datatype. The check constraint should perform an action that it should accept only two letters, e.g. 'AB' or 'XY'. How do I create the check constraint?

Here's how I would do it:

CREATE TABLE twoletters
( code CHAR(2) NOT NULL
, CONSTRAINT twoletters
   CHECK ( CHARACTER_LENGTH(code) = 2
       AND SUBSTRING(code FROM 1 FOR 1)
             BETWEEN 'A' AND 'Z'
       AND SUBSTRING(code FROM 2 FOR 1)
             BETWEEN 'A' AND 'Z' )
);

Notice that I chose CHAR(2). This would allow the CHARACTER_LENGTH condition to be omitted, but I left it in if you cannot change your column from VARCHAR.

You may need to change the standard SQL functions (CHARACTER_LENGTH and SUBSTRING) to match your specific database system. For instance, in SQL Server you would use LEN(code) and SUBSTRING(code,1,1).

This was first published in October 2008

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close