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
       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
  • The 3 daily Scrum questions

    The 2020 Scrum Guide removed all references to the three daily Scrum questions, but does that mean you shouldn't ask them anymore?

  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

Data Center
Content Management