CODEWITHSUNDEEP
sqloracle-databasenovaclient
StevenC
StevenC

Reputation: 109

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!

Upvotes: 0

Views: 1717

Answers (2)

Serg M Ten
Serg M Ten

Reputation: 5606

You may use

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

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 5

Related Questions