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?

U. Windl
  • 125
  • 6

1 Answers1

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;
U. Windl
  • 125
  • 6