sqloracle-databasenovaclient

Case-Insensitive Check Constraint

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!

Solution

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