How to AUTOINCREMENT against another column rather than the whole table in sqlite

Here is an idea that seems to work. Generally speaking though, you can do this like this:

Table1
------
table_key
data_type1
data_type2


Table2
------
table_key
table_key_in_table1 (linked to table1)
data_type1
data_type2
...

And so on.

Based on CL's suggestion to take another look at last_insert_rowid() and now that it's clear that INSTEAD OF triggers are only for VIEWs (see question's comments), here's a working version:

-- We need a view to create the trigger on
CREATE VIEW Container_Auto AS select * from Container;

-- Since it's automatic we need to check if we need to create a root
CREATE TRIGGER Rooter_Auto
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NULL) BEGIN
    INSERT INTO Item(name, parent) VALUES ('ROOT', NULL);
    INSERT INTO Container VALUES (new._id, new.name, last_insert_rowid());
END;
-- If we don't need a root (i.e. the client supplied it), just pass the values through
CREATE TRIGGER Rooter_Transparent
INSTEAD OF INSERT ON Container_Auto WHEN (new.root IS NOT NULL) BEGIN
    INSERT INTO Container VALUES (new._id, new.name, new.root);
END;

-- Tests
INSERT INTO Item VALUES (1, 'ROOT', NULL);
INSERT INTO Container VALUES (1, 'My Box', 1);
INSERT OR IGNORE INTO Container(name) VALUES('Won''t work');
INSERT INTO Container_Auto(name) VALUES('All Automatic');
INSERT INTO Container_Auto(_id, name) VALUES(100, 'Specific ID');
INSERT INTO Container_Auto(name, root) VALUES('Specific root', 1);

-- Results
SELECT '---- Container ----';
SELECT * from Container;
SELECT '---- Item ----';
SELECT * from Item;

Output with comments:

---- Container ----
1|My Box|1          -- everything as we specified
                    -- note the OR IGNORE failed because root IS NULL
3|All Automatic|2   -- the failed insert incremented the rowid as well as created a new root
100|Specific ID|3   -- the supplied _id is used, new root is created
101|Specific root|1 -- the supplied root is used, _id is incremented
---- Item ----
1|ROOT|             -- created manually
2|ROOT|             -- created automatically by INTO Container_Auto(name)
3|ROOT|             -- created automatically by INTO Container_Auto(_id, name)

Notes:

• Using INSERT INTO Table VALUES format helps to catch schema modification bugs since when a new column is added the INSERT will fail instead of ignoring the new column.
• One can still use the Container table to insert into.
• last_insert_rowid() doesn't work when used after an INSERT INTO on a view with INSTEAD OF INSERT, i.e. after INSERT INTO Container_Auto, you'll have to requery the inserted row :(