Error "UNIQUE constraint failed" with composite primary key

With littele SQL(ite) practice, I tried to create a table with a composite primary key: ~~~lang-sqlite3 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: ~~~lang-sqlite3 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: ~~~lang-sqlite3 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?