30

I have a table of persons. Each person has a property and many persons may have a certain property. So this is a many-to-many relationship. This is the schema:

CREATE TABLE persons (
  person_id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(30) NOT NULL,
  lastname varchar(30) NOT NULL,
  PRIMARY KEY (person_id)
);

CREATE TABLE properties (
  property_id int(11) NOT NULL AUTO_INCREMENT,
  property varchar(254) NOT NULL UNIQUE,
  PRIMARY KEY (property_id)
);

CREATE TABLE has_property (
  person_id int(11) NOT NULL,
  property_id int(11) NOT NULL,
  PRIMARY KEY (person_id,property_id),
  FOREIGN KEY (person_id) REFERENCES persons (person_id),
  FOREIGN KEY (property_id) REFERENCES properties (property_id)
);

Now lets say i want to insert to the database this person:

  • firstname:'John'
  • lastname:'Doe'
  • properties:'property_A','property_B','property_C'

persons

+-----------+-----------+----------+
| person_id | firstname | lastname |
+-----------+-----------+----------+
|         1 | John      | Doe      |
+-----------+-----------+----------+

properties

+-------------+------------+
| property_id |  property  |
+-------------+------------+
|           1 | property_A |
|           2 | property_B |
|           3 | property_C |
+-------------+------------+

has_property

+-----------+-------------+
| person_id | property_id |
+-----------+-------------+
|         1 |           1 |
|         1 |           2 |
|         1 |           3 |
+-----------+-------------+

So far the best thing i have thought is to do a regular insert in the persons table:

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');

and then do a select to find the id of the person i just inserted

SELECT person_id FROM persons WHERE firstname='John' AND lastname='Doe';

in order to insert into the other two tables (because i need to know the person_id). But i think there must be a better way, isn't it?

Christos Baziotis
  • 5,845
  • 16
  • 59
  • 80
  • 2
    You can use `SELECT LAST_INSERT_ID()` to retrieve the Id of the last inserted row – Peter van der Wal Oct 31 '13 at 18:49
  • 2
    For those that may say this is a duplicate, I have read this question http://stackoverflow.com/questions/17767973/inserting-data-into-a-table-that-has-a-many-to-many-relationship but it was vague and it didn't help me. – Christos Baziotis Oct 31 '13 at 18:49

1 Answers1

44

Here is what i ended up doing. I hope it helps someone.

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe');
SET @person_id = LAST_INSERT_ID();

INSERT IGNORE INTO properties (property) VALUES ('property_A');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);

INSERT IGNORE INTO properties (property) VALUES ('property_B');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);

INSERT IGNORE INTO properties (property) VALUES ('property_C');
SET @property_id = LAST_INSERT_ID();
INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);
Christos Baziotis
  • 5,845
  • 16
  • 59
  • 80
  • 5
    I know it's been 7yrs but do you remember why you were using `INSERT IGNORE` instead of `INSERT` for `properties` table? Any particular reason? – SkrewEverything Jul 22 '20 at 17:34
  • 1
    According to https://www.mysqltutorial.org/mysql-insert-ignore/ it means that mysql won't throw an error in case the insert fails. I'm not sure why the insert would fail or why you would want to continue if it did. `SET @property_id = LAST_INSERT_ID();` would remain unchanged and would be used in the following ´has_property` inserts – DerpyNerd Nov 30 '20 at 21:13