插入約束問題

使用 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+ ​​則失敗，但如果我在插入語句中手動指定，則沒有問題。

我沒有看到我錯過了什麼

假設您使用的是最新的 12.10 Informix 版本，線上文件指出：

如 INSERT 語句語法圖中所示，並非 SELECT 語句的所有子句和選項都可供您在 INSERT 語句內的查詢中使用。INSERT 語句不支持以下 SELECT 子句和選項：

• 第一和極限
• INTO TEMP、INTO RAW 和 INTO STANDARD 結果表選項
• UNION、UNION ALL、INTERSECT、MINUS 和 EXCEPT 集合運算符。

因此在上下文中使用的語句中FIRST不受支持。但是在查詢中使用不會給出任何語法錯誤，所以可能還有其他事情發生。SELECT``INSERT``FIRST

-- 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或創建 aSEQUENCE來生成order_no值。

引用自：https://dba.stackexchange.com/questions/224556