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