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

Littlefoot · Accepted Answer · 2018-04-30T05:41:18.840

2

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.

edited Apr 30 '18 at 05:41

answered Apr 29 '18 at 20:59

Littlefoot

131,892
15
35
57

I would agree if it was a `NULL` value, but it is being passed in as a `String`. Is there a way to default the incorrect `String` being provided? – Chef1075 Apr 29 '18 at 21:01
1

You might try with a database trigger; I don't think that a declarative constraint is capable of doing that. – Littlefoot Apr 29 '18 at 21:03
I edited my message, providing an example. Have a look. – Littlefoot Apr 29 '18 at 21:12
It was a combination of the user I was logged in as `sys` and not having the trigger set up correctly. Thank you for the great answer :) – Chef1075 Apr 29 '18 at 21:31
1

You're welcome; though, saying that you were connected to SYS - if so, don't do that - don't mess up with SYS nor SYSTEM, they are *special* and not to be used for everyday work. Instead, create your own user, grant it privileges and proceed with what you're doing. If you unintentionally do something wrong with SYS, you'll break the database. – Littlefoot Apr 30 '18 at 05:14
You can also write `insert into test (id, region) values (2, DEFAULT);` – Wernfried Domscheit Apr 30 '18 at 05:34
@Wernfried: hm, I'm *sure* that yesterday someone posted the same answer as your comment (about the DEFAULT keyword) but it *automagically* disappeared during the night. Must be the person who posted it, but I'm not sure why (s)he did that ... Anyway, thank you for the comment; I'll include it into my answer for completeness. – Littlefoot Apr 30 '18 at 05:37
Yes, it was deleted by owner - in spite of a correct answer. You don't have sufficient reputation (10k) yet to see deleted posts. – Wernfried Domscheit Apr 30 '18 at 06:07
@Wernfried: ah, I see - or, should I rather say, *don't see* :) – Littlefoot Apr 30 '18 at 09:22

score 1 · Answer 2 · answered Apr 29 '18 at 21:27

1

You can use select from dual instead of the values keyword. That brings nvl into play.

So, instead of this:

insert into table
(field1, field2, etc)
values
(value1, value2, etc)

do this:

insert into table
(field1, field2, etc)
select nvl(field1, default value1)
, nvl(field2, default value2)
, etc
from dual

answered Apr 29 '18 at 21:27

Dan Bracuk

20,699
4
26
43

I've never seen `nvl` and it might help me answer this question. Thanks – Chef1075 Apr 29 '18 at 21:32

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

2 Answers2