0

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?

Improve this question
2
  • 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. Commented Aug 16, 2023 at 9:15
  • Would you suggest the correct trigger as an answer? I'm fighting with the correct syntax... Commented Aug 16, 2023 at 9:22

1 Answer 1

Reset to default
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;
Improve this answer
2
  • 1
    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 Commented Aug 18, 2023 at 8:27
  • 1
    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. Commented Aug 18, 2023 at 11:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.