Error "UNIQUE constraint failed" with composite primary key

Question

With littele SQL(ite) practice, I tried to create a table with a composite primary key:

CREATE TABLE NAMES (
 TYPE INTEGER NOT NULL,
 SEQ INTEGER NOT NULL,
 NAME VARCHAR(20),
 PRIMARY KEY (TYPE, SEQ)
);

The idea is to store associations from numbers to names, i.e.: SEQ to NAME and back. As there is a variable number of such "classes" I added a TYPE column, that is that the SEQ numbers (and NAMEs) should be unique within a specific TYPE.

I also tried to create a trigger that will automatically assign the "next free" SEQ number within a specific TYPE like this:

CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES
FOR EACH ROW BEGIN
 UPDATE NAMES SET SEQ = (SELECT COUNT(1) FROM NAMES WHERE TYPE = NEW.TYPE)
 WHERE TYPE = NEW.TYPE;
END;

When I insert the first entry, it seems to work, but when I try to add a second one, it fails with

Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19)

like this:

sqlite> select * from NAMES;
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test"); sqlite> select * from NAMES;
0|1|test
sqlite> insert into NAMES (type, seq, name) values (0, -1, "test2");
Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19)
sqlite> insert into NAMES (type, seq, name) values (1, -1, "test2");
sqlite> select * from NAMES;
0|1|test
1|1|test2

So the trigger seems to work for each TYPE exactly once, but maybe it's not working, because the first SEQ should have been 0 instead of 1.

I had also tried a BEFORE rather than AFTER trigger, but that would not change SEQ t all (using -1).

What's wrong with the trigger, or is it the table definition?

• Akina: You use AFTER INSERT trigger. It will fire after the row is saved. But it cannot be saved because of the value duplication. Use BEFORE INSERT trigger and set NEW.SEQ to new needed value, not UPDATE.
• U. Windl: Would you suggest the correct trigger as an answer? I'm fighting with the correct syntax...

Answer 1

The problem was the non-unique WHERE clause missing AND SEQ = NEW.SEQ in the trigger. This version of the trigger works correctly:

CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES
FOR EACH ROW BEGIN
 UPDATE NAMES SET
 SEQ = (SELECT IFNULL(MAX(SEQ), 0) + 1 FROM NAMES WHERE TYPE = NEW.TYPE)
 WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ;
END;

• Lennart - Slava Ukraini: A bit of warning, if for one reason or another an old seq row is deleted you will get a conflict. You may consider using : ... = (SELECT MAX(SEQ)+1 FROM NAMES WHERE ... instead. I don't know SQLLITE, but chances are that it is faster to locate the "last" row instead of counting
• U. Windl: Actually I had realized that (I was copying from another trigger without thinking too much), and I fixed it, so I'll update the answer.