How do you make your check constraint case insensitive in SQL?

This is my piece of code.

CREATE TABLE ORDER_SOURCE(
    OS_ID NUMBER(4),
    OS_DESC VARCHAR2(20),
    CONSTRAINT order_source_os_id_pk PRIMARY KEY (OS_ID),
    CONSTRAINT order_source_os_desc_cc CHECK ((OS_DESC='CATALOG DESCRIPTION') OR (OS_DESC='WEB SITE'))
);

​x
 
CREATE TABLE ORDER_SOURCE(    OS_ID NUMBER(4),    OS_DESC VARCHAR2(20),    CONSTRAINT order_source_os_id_pk PRIMARY KEY (OS_ID),    CONSTRAINT order_source_os_desc_cc CHECK ((OS_DESC='CATALOG DESCRIPTION') OR (OS_DESC='WEB SITE')));​

I want to be able to insert values in lower case too. Example down Below:

INSERT INTO ORDER_SOURCE VALUES(0002,'Web Site');

 
INSERT INTO ORDER_SOURCE VALUES(0002,'Web Site');​

But I can edit my check constraints to add ‘Web Site’ or ‘Catalog’, I just want to try something else. Thanks.

Answer

You may lowercase the column and then compare to lowercase string literals:

CONSTRAINT order_source_os_desc_cc
CHECK (LOWER(OS_DESC) IN ('catalog description', 'web site'))

 
CONSTRAINT order_source_os_desc_ccCHECK (LOWER(OS_DESC) IN ('catalog description', 'web site'))​

Advertisement

Answer