Check constraints: Not null implied?

The following question was asked in a code review meeting this morning: Do check constraints imply not null? It was prompted by a column defined as not mull that also had a check constraint to ensure the column only allowed a discreet set of values. Was the ‘not null’ part redundant?

Surprisingly, no one knew the answer.  My guess was that a check constraint such as “column_name in (‘val1′,’val2′)” would imply the column could not contain null values and so the ‘not null’ piece of the table ddl was redundant. I was a little embarrassed that I didn’t know the answer to such a seemingly basic question. Perhaps more surprisingly, the question was dropped and no one showed any further interest. You would think that a group of developers that make their living working with Oracle databases would want to know the answer. Some folks just don’t give a rip!

SQL> CREATE TABLE TST_CHK (COL VARCHAR2(1), COL2 VARCHAR2(1));

SQL> ALTER TABLE TST_CHK ADD CONSTRAINT TST_CHK1 CHECK (COL2 IN (‘A’, ‘B’)) ENABLE;

SQL> Insert into tst_chk(col, col2) values (‘T’,NULL);

1 row created.

SQL> Insert into tst_chk(col, col2) values (‘T’,'C’);

Insert into tst_chk(col, col2) values (‘T’,'C’);

ERROR at line 1:

ORA-02290: check constraint (DB_ADMIN.TST_CHK1) violated

SQL>

So, check constraints are only enforced when the column is actually populated with a value.

So much for impressing the world with an earth-shattering first post about my job!  More positively, I feel I did my part for the BAAG this morning!

Advertisement

---