首页 文章

违反唯一约束时的Oracle序列值增量

提问于
浏览 次
1

我创建了一个Oracle序列，并在插入新记录时触发自动递增表上的主键列 . 这是我的代码：

CREATE TABLE MOBILE_APP
(
  "MOBILE_APP_ID" NUMBER(9, 0) PRIMARY KEY,
  "NAME" VARCHAR2(60) NOT NULL,
  "DESCRIPTION" VARCHAR2(200),
  CONSTRAINT name_unique UNIQUE (name)
);

CREATE SEQUENCE MOBILE_APP_ID_SEQ
MINVALUE 1
MAXVALUE 999999999
INCREMENT BY 1
START WITH 1
NOCACHE
ORDER
NOCYCLE;

CREATE TRIGGER MOBILE_APP_BR_I
BEFORE INSERT ON MOBILE_APP
FOR EACH ROW
BEGIN
  SELECT MOBILE_APP_ID_SEQ.NEXTVAL INTO :NEW.MOBILE_APP_ID FROM dual;
END;

由于我的触发器是“插入之前”，它将在记录实际插入表之前执行 . 但即使在插入过程中出现唯一约束违规的情况下，我也没想到我的触发器会执行 . 假设表，序列和触发器都是新的，我尝试执行下面的语句两次 .

INSERT INTO MOBILE_APP (name, description) VALUES ('Name', 'Desc');

第一次执行将成功完成，在插入的记录的"mobile_app_id"字段中自动填充值1 . 正如预期的那样，第二次执行将因相对于"name"字段的唯一约束违规而错误输出 . 但是，如果我在不违反唯一约束的情况下插入另一条记录，则会在"mobile_app_id"字段中为插入的记录自动填充值3 - 这意味着在尝试插入期间由于唯一约束违规而失败时，序列的值仍然从1增加到2.我如何防止这种情况？我发现这个other post，但遗憾的是它没有解决问题的方法 . 任何帮助将不胜感激！

oracle sequence unique-constraint

5 回答

  • 0

    这是序列的正常行为 . 您无法保证无间隙序列 . 因为多个事务都可以运行，并且一些事务已经提交并且一些已经回滚，所以我甚至不确定如何使用一个序列来为您提供无间隙的序列，就像您想要的那样 .

    如果你确实需要这样的无间隙序列，你必须确保它在同一个事务中递增：

    CREATE TABLE my_id ( col1 NUMBER );
INSERT INTO my_id ( col1 ) VALUES ( 0 );

    然后执行您的交易：

    DECLARE
    id NUMBER;
BEGIN
  UPDATE my_id SET col1 = col1 + 1
  RETURNING col1 INTO id;

  INSERT INTO mobile_app ... whatever ...

  COMMIT;
END;

    这个问题的一大问题是id表成为瓶颈 . 您不能同时从不同的会话插入mobile_app表 .

    回复于
  • 0

    我非常感谢所提供的所有评论和答案！根据所提供的信息，我认为最好的方法是保持原样，并期望序列可能不会保持无间隙 . 以任何其他方式进行似乎存在太多潜在的缺点 .

    回复于
  • -1
    • 您只需在insert语句中插入序列号即可 .

    INSERT INTO MOBILE_APP（MOBILE_APP_ID，名称，描述）VALUES（MOBILE_APP_ID_SEQ.NEXTVAL，'Name'，'Desc'）;

    • 如果您使用其他应用程序执行此插入，请首先获取id，然后传入insert statment和其他进程 .
    --Create Id.
   CREATE OR REPLACE PROCEDURE MOBILE_APP_ID
   (MOBILE_APP_ID OUT SYS_REFCURSOR )
   IS
      BEGIN
          open MOBILE_APP_ID for 
       SELECT MOBILE_APP_ID_SEQ.NEXTVAL MOBILE_APP_ID
       FROM dual;

   END ;

  --Then get the id.
  int id = MOBILE_APP_ID.MOBILE_APP_ID;

 --Then insert the id.
 INSERT INTO MOBILE_APP (MOBILE_APP_ID,name, description) VALUES (id  ,'Name', 'Desc');
    回复于
  • 3

    如前所述，序列是序列，如果您需要无间隙序列，则不适合 . 首先想一想，你确定你需要一个无间隙的序列吗？如果是这样，建议的解决方案都不是100％安全的间隙 . 如果另一个会话在第一个会话提交其更改之前获取了id，则两者都会出现问题 . 然后第二个会话将具有与第一个会话相同的ID，这当然会导致问题 . 这种风险不应该那么大，但如果你有很多活跃的会话，它仍然会特别存在 .

    不容易获得完全无间隙的序列 . 一种方法可以是例如使用类似于建议的表解决方案，然后捕获任何重复的键错误，然后重试您插入它 . 不好，效率不高，但应该完成任务 .

    回复于
  • 1

    我认为你不需要在这里或任何其他东西，除了下面的代码

    CREATE OR REPLACE TRIGGER AUTO_APPID
BEFORE INSERT
ON MOBILE_APP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
begin

SELECT NVL(MAX(MOBILE_APP_ID),0)+1 INTO :NEW.MOBILE_APP_ID FROM MOBILE_APP;

END ;
    回复于

相关问题