更新时间:2022-12-17 17:28:58
您得到auto_increment列的ID具有函数 LAST_INSERT_ID 的插入行:
所以你可以使用
$ b $ pre code INSERT到SALARY(salary_figure,emp_id)值(30000,LAST_INSERT_ID());
为您的第二个INSERT操作。
如果您想通过触发器将行添加到第三个表中,使用新的 sal_id
和 emp_id
值,您可以在 Salary
表中使用AFTER INSERT触发器,使用emp_id列中的新值以及最后插入的auto_increment id ...与已经提到的LAST_INSERT_ID()函数。
$ b $ pre $ $ codeREATE TRIGGER salary_after_insert AFTER INSERT ON SALARY $
每行
BEGIN
INSERT INTO join_table(emp_id,sal_id)VALUES(NEW.emp_id,LAST_INSERT_ID());
END;
I am using MySQL. My question is how to automatically insert the newly added row into a foreign-key table. An example will clarify my question:
I have two tables Employee and Salary:
CREATE TABLE Employee(
emp_id int NOT NULL AUTO_INCREMENT,
name char(30),
PRIMARY KEY (emp_id)
) ENGINE=innoDB;
CREATE TABLE salary {
sal_id int NOT NULL AUTO_INCREMENT
salary_figure int,
emp_id int,
PRIMARY KEY (sal_id),
FOREIGN KEY REFERENCES Employee(emp_id)
}
Here is the join table :
employee_salary_join Table {
int sal_id,
int emp_id
}
The join table above does not have any foreign key relationship.
Now when I insert an employee into employee table
INSERT into Employee values ("john")
After this statement is executed, a row is created in Employee table that has a pk assigned by database engine.
Now when I insert a row for employee John in salary table as below:
INSERT into SALARY values ("30000", ?????)
How do I get the employee ID of just inserted row for John above and put it here in the place of ?????
Finally, I have a join table, where every time a row is added in salary table, I want the corresponding entry in the join table. This could be done by triggers, but I am not totally clear how to set it up because I need references of both emp_id
and sal_id
for the join table row.
I would also like to know the best practices here when dealing with foreign key inserts. I am using JDBC/mysql driver. I guess that should not affect how we the process the above in sql statements.
You get this ID of your auto_increment column for the inserted row with the function LAST_INSERT_ID: So you can use
INSERT into SALARY (salary_figure, emp_id) values ("30000", LAST_INSERT_ID());
for your second INSERT operation.
If you want to add a row by a trigger into a third table, using the new sal_id
and emp_id
values, you can do that with an AFTER INSERT trigger on the Salary
table using the new value in the column emp_id and the last inserted auto_increment id ... with already mentioned LAST_INSERT_ID() function.
CREATE TRIGGER salary_after_insert AFTER INSERT ON `SALARY`
FOR EACH ROW
BEGIN
INSERT INTO join_table (emp_id, sal_id) VALUES (NEW.emp_id, LAST_INSERT_ID());
END;