1

I have an SQLite database, with a table having an auto increment integer as a primary key, and another table that is linked to this first table, through a foreign key. I don't understand how I can choose which row from the first table is to link with the right row from the second table.

Let me illustrate with an example :

Table student with auto increment primary key (0, 1, 2, ..., 10)

Table courses with these rows : name = 'French', grade = 'B', student (foreign key) = 3

How can I tell the database that the student 3 got B in French? Because I don't know which primary key (auto number) as been assigned to a student, I just know his name.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
Sithered
  • 481
  • 7
  • 23
  • results table: `classID, studentID, result_value`, so basically you'd insert `('spring 2015 french', 'john doe student ID', 'B')`. presumably the student would already have to exist elsewhere in your table, e.g. in an enrolment table. so get the ID of that record: http://stackoverflow.com/questions/8892973/how-to-get-last-insert-id-in-sqlite – Marc B Apr 20 '15 at 14:48

2 Answers2

0

You can use the built-in Sqlite function sqlite3_last_insert_rowid() like this or like this.

It looks as though there are some issues if you are running things mult-threaded so be sure and read up on on proper usage. An example posted in one of the references has this as an alternative. I haven't tried it but it would be worth exploring:

select seq from sqlite_sequence where name="table_name"
Community
  • 1
  • 1
jzapata
  • 1,229
  • 1
  • 12
  • 17
  • That could work, but I have to get other rows than the last created. For example I have a table that has two foreign keys: even if I got one of them from the last_insert_rowid() function, I cannot do the same for the other. – Sithered Apr 20 '15 at 15:13
  • Are all the foreign keys the last ones to be inserted? If so you could do something like this: – jzapata Apr 20 '15 at 15:21
  • No, they are inserted in a random order (from a financial data provider). I can't believe there is not a better way of finding a record. It would mean that any auto primary key cannot be easily used as a foreign key, which does not make sense since primary keys are made for being linked... – Sithered Apr 20 '15 at 15:30
  • I think your only choice is really doing the appropriate queries before you build your insert statement. If you are doing the insert into the other tables you can using sqlite3_last_insert_rowid after each of the insert and then save the ids. Otherwise, just do whatever query you need to to get the ids, then create the insert statement with those ids. It means extra steps I know but the only reliable way. – jzapata Apr 20 '15 at 15:40
  • You might be able to optimize things and just do a single query to get all the ids you need. You could do it as a query with multiple sub-queries. [Take a look](http://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm) - Not sure that will work after looking more at it. Sorry. – jzapata Apr 20 '15 at 15:46
0

Certainly you will always need a natural key that identifies the student for each result. I'm assuming the student's name wouldn't be sufficient because you could have several students with the same name. Typically you would expect students to have a roll number or similar identifier assigned to them. If you don't have sufficient information to identify students uniquely then clearly there is no way to tell which results belong to which student!

nvogel
  • 24,981
  • 1
  • 44
  • 82