Skip to content
Advertisement

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'))
);

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

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.

Advertisement

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'))
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement