Search code examples
sqloracle-databaseconstraints

Minimum length constraint while allowing null values

Let's say we have the following table structure

Name                        Null?    Type          
--------------------------- -------- ------------- 
ID                          NOT NULL NUMBER(15)    
middle_name                          VARCHAR2(255)

How can I enforce with a constraint that values insterted in middle_name must have a minimum length while still allowing null values?

I tried the following constraint

ALTER TABLE users
ADD CONSTRAINT check_middle_name CHECK (length(middle_name) > 1)
;

but it seems that it has a problem with null values as I get the error

SQL Error [2293] [23000]: ORA-02293: cannot validate (middle_name) - check constraint violated
Solution

  • Your code works fine. Here is a db<>fiddle, so something else is going wrong.

    In where and when clauses, NULL results fail the comparison and are treated as "false". CHECK constraints work differently; only explicit "false" values violate the constraint. As a result, NULL values do not result in a violation.

    This is the SQL Standard for check constraints. And it is also how Oracle works.

    Aside from that, eliminating one-character middle names seems like a bad idea. After all, Harry S Truman's middle name was simply "S", to give one example.