CODEWITHSUNDEEP
sqloracle-databaseconstraints
danielnelz
danielnelz

Reputation: 5136

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

Upvotes: 0

Views: 1226

Answers (2)

user5683823
user5683823

Reputation:

The problem is not caused by null in that column. Rather, there is already data in your table that violates the constraint, and that data is rows where middle_name has length 1. For example, you may have 'F' for "John F Kennedy" in the column already.

You must decide what you need to do with those existing values that violate your desired constraint. Either you fix them (change them to null or to longer strings), or you can keep them and enforce the constraint only for future rows. That is very easy to do - add the option novalidate at the end of the add constraint clause in alter table (right before semicolon).

If you add the constraint with the novalidate option, you will also still be able to update old rows, even those where the middle name is a single letter - as long as you only update data in other columns, the constraint is not applied. You will not be able to update the middle name to a single letter even in the old rows though; any new value IN THAT COLUMN must obey the constraint, even if the "new value" applies to an "old row".

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 3

Related Questions