Oracle 不期望唯一约束违反

嗨，问-汤姆-团队，
我有一个独特的约束违反的问题，我不明白。
我有一个表格，可以将问题存储到问卷的项目中。这些问题在特定的年份范围内有效:

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;
/

我已经编写了一个过程来插入数据，并在更新条目时自动更改to_dat。

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;

为了只允许一个给定的变量和一个给定的时间段与类似的问题，我实现了一个唯一的约束四列:

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

长话短说，我在插入数据时遇到问题。使用该过程，以下操作正常，并插入行:

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;

当尝试插入以下行时，由于唯一-约束-如预期的那样发生错误:

BEGIN
    INSERT_QUESTION_PROC('f1','D',2001,NULL,1);
END;  

所以我把问题 (第二个参数) 改为 'E'，并试图插入它。那是当唯一约束违反发生时，我不明白。

BEGIN
    INSERT_QUESTION_PROC('f1','E',2001,NULL,1);
END;  

也许我错过了一些明显的东西。

谢谢你的帮助。

这是你的更新...它试图在不引用 “问题” 值的情况下设置值

我添加了一些调试，以便您可以看到它的发生

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>