CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Bernard
Bernard

Reputation: 4580

How to check if upper and lower case in check constraint are equal?

I have a constraint:

ALTER TABLE account
ADD CONSTRAINT chk_account_type CHECK (Type IN ('saving', 'credit', 'HOME LOAN', 'PERSONAL LOAN', 'TERM DEPOSIT', 'CHECK', 'iSaver', 'SHARE' ) );

When I try to insert 

INSERT INTO Account VALUES ('012878', 123456, 22345678, 'Credit', -1534.52);

It does not work. because Credit starts with uppercase character. How to design it in some how that I can accept saving, Saving, savING.

Upvotes: 1

Views: 2359

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You can do this by changing the case as you check it:

ALTER TABLE account
ADD CONSTRAINT chk_account_type CHECK (lower(Type) IN ('saving', 'credit',
  'home loan', 'personal loan', 'term deposit', 'check', 'isaver', 'share' ));

But allowing variations seems rather odd, as does enforcing these restrictions via a check constraint. It would be more normal, and more flexible, to have the account types in a separate table with a primary key, and make the type column a foreign key against that. Then you can add new account types by adding to that table, rather than having to modify the check constraint, and you can allow case-insensitive look-ups from the account-type table if necessary but always present them consistently.

Something like:

create table account_types(account_type_id number, description varchar2(30),
  constraint account_type_pk primary key (account_type_id));

insert into account_types (account_type_id, description) values (1, 'Saving');
insert into account_types (account_type_id, description) values (2, 'Credit');
insert into account_types (account_type_id, description) values (3, 'Home loan');
...

create table account(account_number number, account_type_id number,
  -- other columns...
  constraint account_pk primary key (account_number),
  constraint account_fk_account_type foreign key (account_type_id)
    references account_types(account_type_id));

Then to create a 'Saving' account:

insert into account values (123, 1);

1 rows inserted.

And if you have an invalid value you get:

insert into account values (124, 42);

SQL Error: ORA-02291: integrity constraint (MYSCHEMA.ACCOUNT_FK_ACCOUNT_TYPE) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"

Upvotes: 5

Related Questions