CODEWITHSUNDEEP
mysqlsqldatabase
alsweet
alsweet

Reputation: 633

How can I insert multiple rows into one table, then into another table using an auto incremented ID? mysql

so I have two tables linked by the key 'skillid':

skills
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| skillid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| skillname | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

students_skills
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| ssid      | int(11) | NO   | PRI | NULL    | auto_increment |
| studentid | int(11) | NO   | MUL | NULL    |                |
| skillid   | int(11) | NO   | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

I'm trying to insert multiple rows into the table skills, and then insert these into student_skills based on the ID that was created. I've been looking into using the LAST_INSERT_ID() function:

INSERT INTO skills (skillid , skillname)
    VALUES(NULL,'being grateful for help'); # generate ID by inserting NULL
INSERT INTO students_skills (ssid, studentid, skillid)
    VALUES(LAST_INSERT_ID(),'1', '2'); # use ID in second table

But I couldn't figure out how to do this for multiple rows at once in one mysql table. I get an error when i simply duplicate the above 4 lines for every row.

ERROR: #1452 - Cannot add or update a child row: a foreign key constraint fails (empology.students_skills, CONSTRAINT students_skills_ibfk_2 FOREIGN KEY (skillid) REFERENCES skills (skillid))

Am I on the right lines or not? I looked into joins also but this method made more sense to me.

Thanks for any help or useful links.

Upvotes: 1

Views: 1745

Answers (2)

Ross Smith II
Ross Smith II

Reputation: 12179

Since students_skills.ssid is an AUTO_INCREMENT column, your second insert looks wrong. It seems you want the following:

INSERT INTO skills (skillid , skillname)
    VALUES(NULL,'being grateful for help'); # generate ID by inserting NULL
INSERT INTO students_skills (ssid, studentid, skillid)
    VALUES(NULL,'1', LAST_INSERT_ID()); # use ID in second table

It would be helpful to see the output of

SHOW CREATE TABLE skills;
SHOW CREATE TABLE students_skills;

to see the FOREIGN KEYs.

UPDATE TO SHOW OUTPUTS

+--------+------------------------------------------------------------------------------
| Table  | Create Table                                                                
+--------+------------------------------------------------------------------------------
| skills | CREATE TABLE `skills` (
  `skillid` int(11) NOT NULL AUTO_INCREMENT,
  `skillname` varchar(30) NOT NULL,
  PRIMARY KEY (`skillid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------

+-----------------+---------------------------------------------------------------------
| Table           | Create Table                                                        
+-----------------+---------------------------------------------------------------------
| students_skills | CREATE TABLE `students_skills` (
  `ssid` int(11) NOT NULL AUTO_INCREMENT,
  `studentid` int(11) NOT NULL,
  `skillid` int(11) NOT NULL,
  PRIMARY KEY (`ssid`),
  KEY `studentid` (`studentid`),
  KEY `skillid` (`skillid`),
  CONSTRAINT `students_skills_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `students` (`studentid`),
  CONSTRAINT `students_skills_ibfk_2` FOREIGN KEY (`skillid`) REFERENCES `skills` (`skillid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+-----------------+--------------------------------------------------------------------

Upvotes: 1

Zane Bien
Zane Bien

Reputation: 23125

You have to make sure to use multiple-row insert syntax so that the LAST_INSERT_ID() stays consistent even though you're auto-incrementing another column:

INSERT INTO skills VALUES (NULL, 'test');

Say the skillid generated was 1, you can then do:

INSERT INTO student_skills VALUES
(NULL, 1, LAST_INSERT_ID()),
(NULL, 2, LAST_INSERT_ID()),
(NULL, 3, LAST_INSERT_ID()),
(NULL, 4, LAST_INSERT_ID());

The value returned by LAST_INSERT_ID() will consistently stay the same (1) throughout all four rows.

However, if you execute multiple inserts as standalone statements, LAST_INSERT_ID() will change as it will instead contain the generated auto-incremented value of each insert:

INSERT INTO student_skills VALUES (NULL, 1, LAST_INSERT_ID());
INSERT INTO student_skills VALUES (NULL, 2, LAST_INSERT_ID());
INSERT INTO student_skills VALUES (NULL, 3, LAST_INSERT_ID());
INSERT INTO student_skills VALUES (NULL, 4, LAST_INSERT_ID());

Where LAST_INSERT_ID() is the generated id of the immediate previous insert.

Take a look at this SQLFiddle Demo

Upvotes: 3

Related Questions