CODEWITHSUNDEEP
oracle-databaseconstraints
Moudiz
Moudiz

Reputation: 7377

check constraint check the column twice

What is wrong in this error ?

alter table INFO add constraint chk_app check (CASE WHEN app IS NULL THEN app = 'A');

If its because app = 'A' I am calling it twice then how to have a check constraint to check if app is null then it should have value A

Upvotes: 0

Views: 224

Answers (2)

wolφi
wolφi

Reputation: 8361

If you want to set the column app to 'A' whenever a row is inserted with a NULL value, I'd use a DEFAULT construct:

 ALTER TABLE info MODIFY (app VARCHAR2(xxx) DEFAULT 'A');

If no value is specified during an INSERT, an 'A' is inserted into column app. Of course, a user can override this behaviour by explicitly inserting a NULL value. This can be prevented by a NOT NULL constraint:

 ALTER TABLE info MODIFY (app VARCHAR2(xxx) DEFAULT 'A' NOT NULL);

From Oracle version 12c on, you can specify the required behaviour more clearly:

 ALTER TABLE info MODIFY (app VARCHAR2(xxx) DEFAULT ON NULL 'A' NOT NULL);

Upvotes: 3

Petr Pribyl
Petr Pribyl

Reputation: 3575

This is bacause check constraint only checks the value, it is not able to change it.

You can simply add default value to app column or write before insert or update trigger like this

create or replace trigger info_briu
before insert or update of info
for each row
begin
  if :new.app is null then
    :new.app := 'A';
  end if;
end;

Upvotes: 3

Related Questions