Good day everyone, i have a question regarding on foreign keys, like is it possible to auto insert on foreign key when i insert data from the main table for example
大家好,我有关于外键的问题,比如当我从主表插入数据时可以自动插入外键,例如
Student_table
|Id | Fname |LName|
|12 | John |Doe |
Quizes_table
ControlNum(auto_increment)|Quiz1|Quiz2|Quiz|Stud_id(fKey)
1 |0 |0 |0 |0 |12
My simple solution was just make 2 insert query insert on students table and quizes table..but using this method im having a hard time when it comes to large amount of data and one to many relationships tables..
我的简单解决方案就是在学生表上插入2个插入查询并查询表...但是使用这种方法我很难处理大量数据和一对多关系表。
so my main question was can i make only one insert query on student table and the foreign key will generates depends on the your insert like for example
所以我的主要问题是我可以在学生表上只进行一次插入查询,并且外键将生成取决于您的插入,例如
insert id on student table like id=14 Fname="myname", Lname = "mylastname" and the quizes table generate stud_id =14 and make the rest field to null or zero if its not possible then i have no other choice but to make each insert query but its kinda hassle for me on the actual real large datas (T_T)
在学生表上插入id,如id = 14 Fname =“myname”,Lname =“mylastname”,quizes表生成stud_id = 14,如果不可能,则将rest字段设为null或0,然后我别无选择每个插入查询,但它对我实际真正的大数据(T_T)有点麻烦
2 个解决方案
#1
2
Sure, you can create a trigger for your student table:
当然,您可以为学生表创建一个触发器:
CREATE TRIGGER student_ins AFTER INSERT ON student
INSERT INTO quizes SET stud_id = NEW.id;
Assuming you have sensible zero values as defaults for the other columns of your quizes table, or else let them be NULL.
假设您的quizes表的其他列具有合理的零值作为默认值,或者让它们为NULL。
I'm not sure how this accomplishes anything, though, since you would need to run another UPDATE query anyway, to populate the quiz scores later.
但是,我不确定这是如何实现的,因为您无论如何都需要运行另一个UPDATE查询,以便稍后填充测验分数。
#2
0
If you inserting a new record from main table into your foreign key table
如果将主表中的新记录插入外键表
You need to use trigger After Insert
您需要在插入后使用触发器
Otherwise you need to construct a normal script to insert a record to your foreign key table
否则,您需要构造一个普通脚本以将记录插入外键表
#1
2
Sure, you can create a trigger for your student table:
当然,您可以为学生表创建一个触发器:
CREATE TRIGGER student_ins AFTER INSERT ON student
INSERT INTO quizes SET stud_id = NEW.id;
Assuming you have sensible zero values as defaults for the other columns of your quizes table, or else let them be NULL.
假设您的quizes表的其他列具有合理的零值作为默认值,或者让它们为NULL。
I'm not sure how this accomplishes anything, though, since you would need to run another UPDATE query anyway, to populate the quiz scores later.
但是,我不确定这是如何实现的,因为您无论如何都需要运行另一个UPDATE查询,以便稍后填充测验分数。
#2
0
If you inserting a new record from main table into your foreign key table
如果将主表中的新记录插入外键表
You need to use trigger After Insert
您需要在插入后使用触发器
Otherwise you need to construct a normal script to insert a record to your foreign key table
否则,您需要构造一个普通脚本以将记录插入外键表