Insert into a table and set another column to autoincremented column value

Question

Let's say I have a simple table:

create table foo
{
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bar INTEGER
}

And I want to insert a new row, such that id == bar where value for id is chosen by the database, a.k.a. auto-increment.

Something like this:

INSERT INTO foo (id, bar) VALUES (NULL, id)

Is it possible do this in one statement?

What is the SQL syntax for that?

Answer 1

In SQLite you can

BEGIN TRANSACTION;
  INSERT INTO foo (id, bar) VALUES (NULL, 0);
  UPDATE foo SET bar = id WHERE _ROWID_ = last_insert_rowid();
COMMIT;

to make sure no other statement gets in the way of your two-statement expression.

Answer 2

You can't have two auto increment fields. You should use a single auto increment field. Given that both fields would always have the same value for every row, there's no reason to have to such fields anyway.

But you can just make trigger which will update another field equal to auto incremented value after inserting row. And delete that trigger when you don't want them to have same values.

CREATE TRIGGER update_foo AFTER INSERT ON foo 
  BEGIN
    UPDATE foo SET bar = NEW.id ;
  END;

When eventually bar will be changed to have not same value as id, then delete trigger

DROP TRIGGER update_foo