Getting the primary key back from a SQL insert with SQLite

Question

I have a SQL table set that looks like this

create table foo ( 
    id int primary key asc, 
    data datatype );

create table bar ( 
    id int primary key asc, 
    fk_foo int,
    foreign key(foo_int) references foo(id));

Now, I want to insert a record set.

insert into table foo (data) values (stuff);

But wait - to get Bar all patched up hunkydory I need the PK from Foo. I know this is a solved problem.

What's the solution?

SysAdmin · Accepted Answer · 2010-05-04 07:00:52Z

9

try this

SELECT last_insert_rowid()

answered May 4, 2010 at 7:00

SysAdmin

5,6058 gold badges35 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike W. Over a year ago

I know this is an older question/answer, but note that this does NOT work correctly with the create statements define above. last_insert_rowid returns the last rowid of the specified table. But in create table statements above, the id column is defined as 'int primary key'. In this case, id is NOT an alias for rowid. That's only the case if id is declared as 'integer primary key'. This is one (the only?) case where sqlite requires a very specific column declaration. 'int' isn't good enough, it MUST be 'integer'. Otherwise, id and rowid are NOT the same colulmn.

Collectives™ on Stack Overflow

Getting the primary key back from a SQL insert with SQLite

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related