主页 / dba / 问题 / 224556
Accepted
Tsukasa
Asked: 2018-12-11 06:00:34 +0800 CST

插入约束问题

  • 772

使用 IBM Informix 数据库。如果我手动指定 order_no，该语句有效，但如果我尝试在查询中递增它，它就会失败。

表结构

patkey serial
notetype char(4)
order_no int

约束是 patkey、notetype 和 order_no

我可以手动插入运行以下命令并增加 order_no

insert into patnotes (patkey, notetype, order_no) values (5000, 'GEN', 3)

我想在插入语句中增加它，但我得到了重复的违规并且不明白如何。

这是我试图用来增加 order_no 的插入语句。

INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1 
    p.personkey, 
    'GEN' as notetype, 
    CASE WHEN n.order_no is null then 1 else (n.order_no + 1) end as order_no
FROM 
    person p 
LEFT JOIN 
    patnotes n 
    ON p.personkey = n.patkey 
    AND n.notetype = 'GEN'
WHERE 
    p.patid = '5000'
ORDER BY 
    n.order_no DESC

检查表是否存在以下条目

5000, GEN, 1
5000, GEN, 2

下一个顺序应该是 3。

运行上述插入语句的选择部分给出：

5000, GEN, 3

这是正确的，但不符合约束条件。如果我在第一个插入语句中手动输入这些，就没有问题。

现在下一个奇怪的问题是，如果我清除这些条目，那么第一个条目是 order_no 1。

这将为 order_no 1 和 2 添加条目，但对于 3+ 会失败，但如果我仅在插入语句中手动指定它，则没有问题。

我没有看到我错过的东西

informix

1 个回答

  1. Best Answer

    假设您使用的是最新的 12.10 Informix 版本，在线文档指出：

    如 INSERT 语句语法图中所示，并非 SELECT 语句的所有子句和选项都可供您在 INSERT 语句内的查询中使用。INSERT 语句不支持以下 SELECT 子句和选项：

    • 首先和限制
    • INTO TEMP、INTO RAW 和 INTO STANDARD 结果表选项
    • UNION、UNION ALL、INTERSECT、MINUS 和 EXCEPT 集合运算符。

    上下文中使用的语句FIRST不支持So。但是在查询中使用不会给出任何语法错误，所以可能发生了其他事情。SELECTINSERTFIRST

    -- The setup
CREATE TABLE patnotes
(
     patkey    SERIAL
    , notetype CHAR(4)
    , order_no INTEGER
);

CREATE UNIQUE INDEX patnotes_udx1 ON patnotes(patkey, notetype, order_no);

INSERT INTO patnotes VALUES (5000, 'GEN', 1);
INSERT INTO patnotes VALUES (5000, 'GEN', 2);

CREATE TABLE person
(
      personkey INTEGER
    , patid     CHAR(4)
);

INSERT INTO person VALUES (4999, '4999');
INSERT INTO person VALUES (5000, '5000');
INSERT INTO person VALUES (5001, '5001');

-- The insert query
INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1 
    p.personkey
    , 'GEN' AS notetype
    , CASE 
        WHEN n.order_no IS NULL THEN 1 
        ELSE (n.order_no + 1) 
      END AS order_no
FROM 
    person p 
LEFT JOIN 
    patnotes n 
    ON p.personkey = n.patkey 
    AND n.notetype = 'GEN'
WHERE 
    p.patid = '5000'
ORDER BY 
    n.order_no DESC;

    当我们运行上面的代码时，INSERT我们得到：

    239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:patnotes_udx1).
100: ISAM error:  duplicate value for a record with unique key.

    如果我们删除UNIQUE约束并运行INSERT多次，我们将始终获得相同的 column 值order_no。

    -- remove the UNIQUE
DROP INDEX patnotes_udx1;

-- Run the insert multiple times and we get
SELECT * FROM patnotes;
patkey notetype    order_no

  5000 GEN                1
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2

    它不断插入相同的计算值order_no。似乎是一个错误。您应该使用 Informix 支持打开缺陷。

    作为解决方法，您可以使用MAX来计算order_no：

    INSERT INTO patnotes (patkey, notetype, order_no)      
SELECT
    p.personkey
    , 'GEN' AS notetype
    , (MAX(NVL(n.order_no,0)) + 1) AS order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
GROUP BY
    p.personkey, notetype
;

    请注意，如果您有多个会话试图同时插入新值，这种方法将失败得很惨。您应该使用SERIAL或创建一个SEQUENCE来生成order_no值。

    • 2

相关问题