When Oracle constraint is violated, set default? How do I do this?

Question

I'm having some issues with setting a default value in a Oracle database.

I want to set the default value for my region to NW: region CHAR(2) DEFAULT ('NW'),

I also have a reference check to make sure all values in region are 2 characters: CONSTRAINT check_region CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));

However, I'm getting this error when I'm inserting data which does not fit my constraint. I have a row of data which has 'NULL' for a value so it should not be inserted ... but it should default to the default value of NW ... right?

Error starting at line : 16 in command -
INSERT into acctmanager
 (amid, amfirst, amlast, amedate, amsal, amcomm, region) VALUES ('L500','MANDY','LOPEZ','01-OCT-09', 47000, 1500, 'NULL')
Error report -
ORA-02290: check constraint (SYS.CHECK_REGION) violated

I understand the error should pop up ... but should the value not default to what I have defined in my schema for region ? I've tried looking here but I'm still getting the error with inserting.

Any suggestions?

Here is my entire create table statement:

DROP TABLE ACCTMANAGER CASCADE CONSTRAINTS;

CREATE TABLE acctmanager
(amid CHAR(4),
 amfirst VARCHAR2(12)  NOT NULL,
 amlast VARCHAR2(12)  NOT NULL,
 amedate DATE DEFAULT SYSDATE NOT NULL,
 amsal NUMBER(8,2),
 amcomm NUMBER(7,2),
 region CHAR(4) DEFAULT ('NW'),
  CONSTRAINT PK_acctmanager PRIMARY KEY (amid),
  CONSTRAINT check_region CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));

INSERT into acctmanager
 (amid, amfirst, amlast, amedate, amsal, amcomm, region) VALUES ('T500','NICK','TAYLOR','05-SEP-09', 42000, 3500, 'NE');
INSERT into acctmanager
 (amid, amfirst, amlast, amedate, amsal, amcomm, region) VALUES ('L500','MANDY','LOPEZ','01-OCT-09', 47000, 1500, 'NULL');
INSERT into acctmanager
 (amid, amfirst, amlast, amedate, amsal, amcomm, region) VALUES ('J500','SAMMIE','JONES','DEFAULT' , 39500, 2000, 'NW');

Answer 1

VALUES ('L500','MANDY','LOPEZ','01-OCT-09', 47000, 1500, 'NULL')

Not 'NULL', but NULL (without single quotes).

[EDIT]

Here's an example of a trigger:

SQL> create table test
  2    (id      number,
  3     region  varchar2(2) default 'NW'
  4    );

Table created.

SQL> create or replace trigger trg_biu_test
  2    before insert or update on test
  3    for each row
  4  begin
  5    if :new.region not in ('N', 'NW', 'NE') then
  6       :new.region := 'NW';
  7    end if;
  8  end;
  9  /

Trigger created.

Testing: if the REGION name is longer than VARCHAR2 (as that's how the column is declared, insert will fail regardless the trigger):

SQL> insert into test (id, region) values (1, 'what?');
insert into test (id, region) values (1, 'what?')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TEST"."REGION" (actual: 5, maximum: 2)

A shorter (up to 2 characters) will be OK:

SQL> insert into test (id, region) values (1, 'xx');

1 row created.

Inserting NULL into region will, actually, insert the default value:

SQL> insert into test (id) values (2);

1 row created.

SQL>
SQL> select * From test;

        ID RE
---------- --
         1 NW
         2 NW

SQL>

[EDIT #2: the DEFAULT keyword]

Yet another option is to use the DEFAULT keyword, such as

SQL> insert into test (id, region) values (3, default);

1 row created.

SQL> select * From test where id = 3;

        ID RE
---------- --
         3 NW

Thanks to Wernfried for the comment.