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

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.

CodePudding user response：

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

CodePudding user response：

upper() or lower() would be the simplest approach, but just for completeness, from 12.2 there is also collate binary_ci (case-insensitive) or binary_ai (additionally accent-insensitive).

create table order_source
( os_id      number(4) constraint order_source_os_id_pk primary key
, os_desc    varchar2(20) not null
             constraint order_source_os_desc_cc
                        check (os_desc collate binary_ci in ('CATALOG DESCRIPTION', 'WEB SITE'))
);