0

Is there anyway that we can set a constraint in database table level to have upper or lower case values for certain columns? When we create a table, we can set NOT NULL to avoid having null values on a column. Same way, can we do that for either uppercase or lower case?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
user3123690
  • 1,053
  • 5
  • 17
  • 27

2 Answers2

4

You can do that using a check constraint: 

create table foo
(
   only_lower varchar(20) not null check (lower(only_lower) = only_lower),
   only_upper varchar(20) not null check (upper(only_upper) = only_upper)
);
  • It worked. Do we need NOT NULL statement if we accept either null or upper only? – user3123690 Oct 27 '15 at 18:39
  • @user3123690 If you want to allow `null` values, then of course you don't need the `not null` constraint. –  Oct 27 '15 at 19:13
0

I had almost same case, tried with check constraint, but if the user is not mentioning it as UPPER() or LOWER() it gives error so I took TRIGGER route as below code.

--creating table
create table user_name (
first_name varchar2(50),
last_name varchar2(50));

--creating trigger
CREATE OR REPLACE TRIGGER TRG_USER_NAME_IU
  BEFORE INSERT OR UPDATE ON USER_NAME
  FOR EACH ROW
BEGIN
  :NEW.FIRST_NAME := UPPER(:NEW.FIRST_NAME);
  :NEW.LAST_NAME := UPPER(:NEW.LAST_NAME);
END;
/

Can test and share feedback or comments

Pavn
  • 160
  • 1
  • 10