Hi Ask-Tom-Team,
I have a problem with an unique constraint violation I do not understand.
I have a table that stores questions to item of a questionnaire. The questions are valid for a specific range of years:
CREATE TABLE question (
id NUMBER
CONSTRAINT nnc_quest_id NOT NULL,
variable_id VARCHAR2(7)
CONSTRAINT nnc_quest_variable_id NOT NULL,
question VARCHAR2(255)
CONSTRAINT nnc_quest_question NOT NULL,
from_year SMALLINT
CONSTRAINT nnc_quest_from_year NOT NULL,
to_year SMALLINT,
creat_id NUMBER
CONSTRAINT nnc_quest_creat_id NOT NULL,
creat_dat TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
CONSTRAINT nnc_quest_creat_dat NOT NULL,
act_id NUMBER,
act_dat TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
);
CREATE UNIQUE INDEX idx_quest_id ON
question (
id ASC
);
ALTER TABLE question ADD CONSTRAINT pk_quest PRIMARY KEY ( id );
CREATE SEQUENCE quest_id_seq START WITH 1 NOCACHE ORDER;
CREATE OR REPLACE TRIGGER quest_id_trg BEFORE
INSERT ON question
FOR EACH ROW
WHEN ( new.id IS NULL )
BEGIN
:new.id := quest_id_seq.nextval;
END;
/
I have written a procedure to insert data and automatically change the to_dat when an entry is updated.
create or replace PROCEDURE INSERT_QUESTION_PROC
(
IN_VARIABLE_ID IN VARCHAR2
, IN_QUESTION IN QUESTION.QUESTION%TYPE
, IN_FROM_YEAR IN QUESTION.FROM_YEAR%TYPE
, IN_TO_YEAR IN QUESTION.TO_YEAR%TYPE
, IN_CREAT_ID IN QUESTION.CREAT_ID%TYPE
, IN_CREAT_DAT IN QUESTION.CREAT_DAT%TYPE DEFAULT SYSTIMESTAMP
) AS
BEGIN
UPDATE question
SET to_year = CASE
WHEN (in_from_year > from_year) THEN in_from_year - 1
ELSE in_from_year
END
, act_id = in_creat_id, act_dat = in_creat_dat
WHERE variable_id = in_variable_id
and to_year IS NULL;
INSERT INTO question
(variable_id, question, from_year, to_year, creat_id)
VALUES (in_variable_id, in_question, in_from_year, in_to_year, in_creat_id);
END INSERT_QUESTION_PROC;
To permit only one entry for a given variable and a given period of time with a similar question I implemented a unique constraint on the four columns:
ALTER TABLE question
ADD CONSTRAINT um_quest UNIQUE ( variable_id,
question,
from_year,
to_year );
CREATE UNIQUE INDEX idx_quest_variable_id_question_from_year_to_year ON
question (
variable_id ASC,
question ASC,
from_year ASC,
to_year ASC
);
To cut a long story short, I have a problem when inserting data. Using the procedure the following works fine and the rows are inserted:
BEGIN
INSERT_QUESTION_PROC('f1','A',2001,NULL,1);
INSERT_QUESTION_PROC('f1','B',2001,NULL,1);
INSERT_QUESTION_PROC('f1','C',2001,NULL,1);
INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
END;When trying to insert the following row, an error occurs due to the unique-constraint - as expected:
BEGIN
INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
END;
So I changed the question (second parameter) to 'E' and tried to insert it. That is when the unique constraint violation occurs that I do not understand.
BEGIN
INSERT_QUESTION_PROC('f1','E',2001,NULL,1);
END;
Maybe I am missing something obious.
Thanks for your help.
It is your update ... it is trying to set values without reference to the "question" value
I added some debugging so you can see it happening
SQL>
SQL>
SQL> CREATE TABLE question (
2 id NUMBER
3 CONSTRAINT nnc_quest_id NOT NULL,
4 variable_id VARCHAR2(7)
5 CONSTRAINT nnc_quest_variable_id NOT NULL,
6 question VARCHAR2(255)
7 CONSTRAINT nnc_quest_question NOT NULL,
8 from_year SMALLINT
9 CONSTRAINT nnc_quest_from_year NOT NULL,
10 to_year SMALLINT,
11 creat_id NUMBER
12 CONSTRAINT nnc_quest_creat_id NOT NULL,
13 creat_dat TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
14 CONSTRAINT nnc_quest_creat_dat NOT NULL,
15 act_id NUMBER,
16 act_dat TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
17 );
Table created.
SQL> CREATE UNIQUE INDEX idx_quest_id ON
2 question (
3 id ASC
4 );
Index created.
SQL> ALTER TABLE question ADD CONSTRAINT pk_quest PRIMARY KEY ( id );
Table altered.
SQL>
SQL> CREATE SEQUENCE quest_id_seq START WITH 1 NOCACHE ORDER;
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER quest_id_trg BEFORE
2 INSERT ON question
3 FOR EACH ROW
4 WHEN ( new.id IS NULL )
5 BEGIN
6 :new.id := quest_id_seq.nextval;
7 END;
8 /
Trigger created.
SQL>
SQL> create or replace procedure PPP(m varchar2, s int) is
2 begin
3 dbms_output.put_line(m||':'||s);
4
5 for i in ( select * from question )
6 loop
7 dbms_output.put_line(i.variable_id||','||i.question||','||i.from_year||','||nvl(i.to_year,-1));
8 end loop;
9 dbms_output.put_line('----------------');
10 end;
11 /
Procedure created.
SQL>
SQL>
SQL> create or replace PROCEDURE INSERT_QUESTION_PROC
2 (
3 IN_VARIABLE_ID IN VARCHAR2
4 , IN_QUESTION IN QUESTION.QUESTION%TYPE
5 , IN_FROM_YEAR IN QUESTION.FROM_YEAR%TYPE
6 , IN_TO_YEAR IN QUESTION.TO_YEAR%TYPE
7 , IN_CREAT_ID IN QUESTION.CREAT_ID%TYPE
8 , IN_CREAT_DAT IN QUESTION.CREAT_DAT%TYPE DEFAULT SYSTIMESTAMP
9 ) AS
10 BEGIN
11 dbms_output.put_line('about to update');
12 UPDATE question
13 SET to_year = CASE
14 WHEN (in_from_year > from_year) THEN in_from_year - 1
15 ELSE in_from_year
16 END
17 , act_id = in_creat_id, act_dat = in_creat_dat
18 WHERE variable_id = in_variable_id
19 and to_year IS NULL;
20 ppp ('after update ',sql%rowcount);
21 INSERT INTO question
22 (variable_id, question, from_year, to_year, creat_id)
23 VALUES (in_variable_id, in_question, in_from_year, in_to_year, in_creat_id);
24
25 ppp ('after insert',null);
26
27 END INSERT_QUESTION_PROC;
28 /
Procedure created.
SQL>
SQL>
SQL> ALTER TABLE question
2 ADD CONSTRAINT um_quest UNIQUE ( variable_id,
3 question,
4 from_year,
5 to_year );
Table altered.
SQL>
SQL> set serverout on
SQL> BEGIN
2 INSERT_QUESTION_PROC('f1','A',2001,NULL,1);
3 INSERT_QUESTION_PROC('f1','B',2001,NULL,1);
4 INSERT_QUESTION_PROC('f1','C',2001,NULL,1);
5 INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
6 INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
7 END;
8 /
about to update
after update :0
----------------
after insert:
f1,A,2001,-1
----------------
about to update
after update :1
f1,A,2001,2001
----------------
after insert:
f1,A,2001,2001
f1,B,2001,-1
----------------
about to update
after update :1
f1,A,2001,2001
f1,B,2001,2001
----------------
after insert:
f1,A,2001,2001
f1,B,2001,2001
f1,C,2001,-1
----------------
about to update
after update :1
f1,A,2001,2001
f1,B,2001,2001
f1,C,2001,2001
----------------
after insert:
f1,A,2001,2001
f1,B,2001,2001
f1,C,2001,2001
f1,D,2001,-1
----------------
about to update
after update :1
f1,A,2001,2001
f1,B,2001,2001
f1,C,2001,2001
f1,D,2001,2001
----------------
after insert:
f1,A,2001,2001
f1,B,2001,2001
f1,C,2001,2001
f1,D,2001,2001
f1,D,2001,-1
----------------
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
2 INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
3 END;
4 /
about to update
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.UM_QUEST) violated
ORA-06512: at "MCDONAC.INSERT_QUESTION_PROC", line 12
ORA-06512: at line 2
SQL>
SQL> BEGIN
2 INSERT_QUESTION_PROC('f1','E',2001,NULL,1);
3 END;
4 /
about to update
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.UM_QUEST) violated
ORA-06512: at "MCDONAC.INSERT_QUESTION_PROC", line 12
ORA-06512: at line 2
SQL>