MySQL auto assign foreign key ID

Question

I have a main table called results. E.g.

CREATE TABLE results (
    r_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    r_date DATE NOT NULL,
    system_id INT NOT NULL,
    FOREIGN KEY (system_id) REFERENCES systems(s_id) ON UPDATE CASCADE ON DELETE CASCADE
);

The systems table as:

 CREATE TABLE systems (
    s_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    system_name VARCHAR(50) NOT NULL UNIQUE
);

I'm writing a program in Python with MySQL connector. Is there a way to add data to the systems table and then auto assign the generated s_id to the results table?

I know I could INSERT into systems, then do another call to that table to see what the ID is for the s_name, to add to the results table but I thought there might be quirk in SQL that I'm not aware of to make life easier with less calls to the DB?

Does this answer your question? [Get the new record primary key ID from MySQL insert query?](https://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query) — Serg, Apr 26 '21 at 07:31

score 1 · Answer 1 · answered Apr 26 '21 at 21:53

You could do what you describe in a trigger like this:

CREATE TRIGGER t AFTER INSERT ON systems
FOR EACH ROW
  INSERT INTO results SET r_date = NOW(), system_id = NEW.s_id;

This is possible only because the columns of your results table are easy to fill in from the data the trigger has access to. The auto-increment fills itself in, and no additional columns need to be filled in. If you had more columns in the results table, this would be harder.

You should read more about triggers:

MySQL auto assign foreign key ID

1 Answers1