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).

Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close