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 last published in October 2008

Dig Deeper on Oracle development languages

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close