Insert constraint issue

Question

Using IBM Informix database. If I specify order_no manually, the statement works but if I try to increment it within the query, it fails.

Table structure

patkey serial
notetype char(4)
order_no int

The constraint is patkey, notetype and order_no

I can manually insert running the following and increment the order_no

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

I wanted to increment it within the insert statement but I get a duplicate violation and don't understand how.

Here is the insert statement that I'm trying to use to increment the 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

Checking the table the following entries exist

5000, GEN, 1
5000, GEN, 2

The next order should be 3.

Running the select portion of the above insert statement gives:

5000, GEN, 3

This is correct but fails the constraint. If I enter these manually in the first insert statement, there is no issue.

Now the next odd issue is if I clear out these entries so the first entry is order_no 1.

This will add entries for order_no 1 and 2 but then fails for 3+ but again if I specify this manually in just an insert statement, no issues.

I'm not seeing what I'm missing

Answer 1

Assuming you are using a recent 12.10 Informix version, the online documentation states:

As indicated in the INSERT statement syntax diagram, not all clauses and options of the SELECT statement are available for you to use in a query within an INSERT statement. The following SELECT clauses and options are not supported in an INSERT statement:

• FIRST and LIMIT
• INTO TEMP, INTO RAW, and INTO STANDARD result table options
• UNION, UNION ALL, INTERSECT, MINUS, and EXCEPT set operators.

So FIRST is not supported in SELECT statements used in an INSERT context. But using FIRST in the query does not give any syntax error, so maybe something else is going on.

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

When we run the above INSERT we get:

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.

If we remove the UNIQUE constraint and run the INSERT multiple times we keep getting the same value for 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

It keeps inserting the same calculated order_no . Seems like a bug. You should open a defect with Informix support.

As a workaround you can use MAX to calculate 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
;

Please do note that this approach will fail miserably if you have multiple sessions trying to insert new values concurrently. You should use SERIAL or create a SEQUENCE to generate the order_no values.