如何在MySQL或JDBC表中自动插入外键引用？ [英] how to automatically insert foreign key references in tables in mysql or JDBC?

问题描述

我正在使用MySQL。我的问题是如何自动将新添加的行插入到外键表中。一个例子将澄清我的问题：

我有两个表Employee and Salary：

$ pre codeREATE TABLE Employee（
emp_id int NOT NULL AUTO_INCREMENT，
name char（30），
PRIMARY KEY（emp_id）$ b $ ENGINE = innoDB;

CREATE TABLE salary {
sal_id int NOT NULL AUTO_INCREMENT
salary_figure int，
emp_id int，
PRIMARY KEY（sal_id），
FOREIGN关键参考员工（emp_id）
}

以下是连接表：

$ b $ pre $ lt; code> employee_salary_join表{
int sal_id，
int emp_id
}

上面的连接表没有任何外键关系。

现在，当我将员工插入员工表中时， Employee的值（john）

执行这个语句后，在Employee表中创建一行有一个pk由数据库引擎分配。

现在，当我在salary table中为员工John插入一行时，如下所示：

插入SALARY值（30000，?????）

如何获得John上面插入行的员工ID，并将其放在 ?????

最后，我有一个连接表，每次在salary表中添加一行时，我都希望连接表中的相应条目。这可以通过触发器完成，但我不完全清楚如何设置它，因为我需要 emp_id 和 sal_id


我也想知道在处理外部键插入时的最佳实践。我正在使用JDBC / MySQL驱动程序。我猜这应该不会影响我们在sql语句中的上述过程。

解决方案

您得到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;

这篇关于如何在MySQL或JDBC表中自动插入外键引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！