Check Constraints 2

[Michael42]

Hello,

If a column has a check constraint of TYPEX IN ('Y', 'N'), then only Y and N values are allowed and other receive a constraint violation error message.

However, I have a table where the specified the constraints to be: TYPEX IN (NULL, 'Y', 'N').

What the NULL value appears to act as a wildcard and lets anything value ('y', 'Y', '1', 'K'...) be put into the column.

So my question is:

1. Is this a little known "feature" and we need to rethink how to use those columns without a NULL value?

2. Is there another way to specify the check constraint to permit a NULL value be in the list of allowable values?

Thanks,

Michael42

 

[SantaMufasa]

Michael,

The method to achieve your objective is to 1) ensure that there is no NOT NULL constraint on the TYPEX column and 2) set up the constraint as you did originally:

create table michael (typex varchar2(1) check (typex in ('Y','N')));

Table created.

SQL> insert into michael values ('Y');

1 row created.

SQL> insert into michael values ('N');

1 row created.

SQL> insert into michael values (NULL);

1 row created.

SQL> insert into michael values ('K');
insert into michael values ('K')
*
ERROR at line 1:
ORA-02290: check constraint (TEST.SYS_C0013545) violated

As you can see, above, the valid values of 'Y','N',and NULL pass muster, but any other value (e.g., 'K') does not pass the test.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Michael,

yes, try this out.

CREATE TABLE michael
(michael_test VARCHAR2(1));

ALTER TABLE michael
ADD CONSTRAINT chk_yes_no_or_null
CHECK (michael_test IS NULL OR michael_test = 'Y' OR michael_test = 'N');

INSERT INTO michael michael_test VALUES(NULL);
INSERT INTO michael michael_test VALUES (3);
INSERT INTO michael michael_test VALUES ('X');

The null will go in ok, but the 3 and the X will bounce on the constraint. You have to check for the state of being null or not being null, you can't just say null.

See Santa Mufasa's excellent explanation of nulls in this forum.

Regards

Tharg

Grinding away at things Oracular

 

[Michael42]

Santa, Tharg,

Great suggestions. They were very useful!

Michael