Ask The Oracle Expert: Questions & Answers

How to create an SQL CHECK constraint for two letters

How to create an SQL CHECK constraint for two letters

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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