Case-Insensitive Check Constraint

1.5k views Asked by At

Created the following table:

CREATE TABLE VEHICLES

(vehicleVIN VARCHAR(30) PRIMARY KEY,

vehicleType VARCHAR(30) NOT NULL CHECK (vehicleType IN ('compact', 'midsize', 'fullsize', 'suv', 'truck')),

vehicleWhereFrom VARCHAR(30) NOT NULL CHECK (vehicleWhereFrom IN ('maryland','virginia','washington, d.c.'));

When running the insert commands, entries that have capital letters (ex: Compact, COMPACT, Maryland, VIRGINIA, etc.) violate the check constraint (error ORA-02290). How do I make the check constraint case-insensitive? Desired results would be that data inserted is accepted, regardless of case used, as long as the word is spelled correctly. Using Oracle database via NOVA. Thanks!

2

There are 2 answers

2
Vamsi Prabhala On BEST ANSWER

Use lower to check the lower case version of the column.

CHECK vehicleType VARCHAR(30) NOT NULL CHECK (lower(vehicleType) IN ('compact', 'midsize', 'fullsize', 'suv', 'truck')),
CHECK vehicleWhereFrom VARCHAR(30) NOT NULL CHECK (lower(vehicleWhereFrom) IN ('maryland','virginia','washington, d.c.'));
0
Serg M Ten On

You may use

CHECK (REGEXP_LIKE(vehicleType,'compact|midsize|fullsize|suv|truck','i'))