SQLite : Choose the right record to link when auto increment primary key

0
votes

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.

2
sqldatabasesqliteforeign-keys
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: stackoverflow.com/questions/8892973/… - Marc B

2 Answers

0
votes

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"
0
votes

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!