Best way to get value for inserting into foreign key SQLite c#

Question

When you have two tables with a foreign key for example

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
  department_name VARCHAR
);

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

Then when you insert data like this:

INSERT INTO departments VALUES (30, 'HR');
INSERT INTO departments VALUES (999, 'Sales');

INSERT INTO employees VALUES (10000, 'Smith', 'John', 30);
INSERT INTO employees VALUES (10001, 'Anderson', 'Dave', 999);

What is the best way of finding the values 30 and 999 when inserting into the emplyees table?

I don't think there is any good way. You have to know what departments the employees are in when you do the insert. For example, if you had a form in your client application, you'd have a field for first name, a field for last name, and (probably) a drop-down list to select a department. This data goes to the database to be saved (so, at some point, an insert statement such as the ones you wrote here is executed) — topsail, Jan 29 '23 at 00:50
I've forgot to add it but I've been relying on the `autoincremeant` feature to get id's. So is it best to execute another select statement to find the Id of the department? And is there any way of adding this as like a nested sql query (im not sure what the proper name is) — Huon Swales, Jan 29 '23 at 01:31
It is generally possible to get the last inserted id. Different databases have different mechanisms for this. It's not a good fit for departments. How often does an employee happen to work in the very last department you created? This only works for the unique case where you just inserted a new department - something that happens only once for each department and never again. — topsail, Jan 29 '23 at 01:41
See here: https://stackoverflow.com/questions/2127138/how-to-retrieve-the-last-autoincremented-id-from-a-sqlite-table — topsail, Jan 29 '23 at 01:42
Just had a look and i dont think that will work for my project but it did lead me to find sqlite-net wich is a more object-oriented sqlite nuget package that returns the id of an object when inserted to the database. Thank you for your help! — Huon Swales, Jan 29 '23 at 01:53
Okay great. That is a good solution. I was going to say you'd probably want to wrap your statements in a transaction if you were doing an insertion and then another query to get the last id. This sounds better. I've put another "naive" solution below as well. — topsail, Jan 29 '23 at 01:57
Please clarify via edits, not comments. Please delete & flag obsolete comments. Debug questions require a [mre]. "best"doesn't mean anything in particular. [ask] [Help] — philipxy, Jan 29 '23 at 09:01

topsail · Accepted Answer · 2023-01-29T02:03:29.337

Here is an example using the sqlite last_insert_id() function (I'm going to clean this up to show that it really is an auto-number value, rather than a department number you already know):

begin transaction;

insert into departments (department_name) values ('HR');
insert into employees values (10000, 'Smith','John',last_insert_rowid());

insert into departments (department_name) values ('Sales');
insert into employees values (10001, 'Anderson','Dave',last_insert_rowid());

commit;

Another strategy is to use your departments names, so you can avoid worrying about knowing the ids at all (I will assume you add a unique constraint so that department names must be unique - which is sensible):

insert into departments values (30, 'HR');
insert into departments values (999, 'Sales');

insert into employees 
    select 
        10000,
        'Smith',
        'John',
        (select department_id 
            from departments 
            where department_name = 'HR');

insert into employees 
    select 
        10001,
        'Anderson',
        'Dave',
        (select department_id 
            from departments where department_name = 'Sales');

Best way to get value for inserting into foreign key SQLite c#

1 Answers1