MySQL 更新其他表
我希望能够将数据插入到 t1
中,并使用主键作为 t2
中的外键将数据填充到表 t2
中。
基本上,当我 INSERT INTO t1 (first_name, last_name) values ("blah", "blah");
然后执行 SELECT * FROM t2;
时,我当前的设置是怎样的? > t2
它说 t2
为 Empty Set (0.00 sec)
?难道它不应该至少显示默认的 id
1 吗?
t1
:
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| first_name | varchar(20) | NO | | NULL | |
| last_name | varchar(20) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+------------+------------------+------+-----+---------+----------------+
t2
:
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| address | varchar(50) | NO | | NULL | |
| id | int(10) unsigned | NO | MUL | NULL | |
| last_name | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
I want to be able to insert data into t1
and have data get populated in table t2
with the primary key as a foreign key in t2
.
Basically, how come in my current setup when I INSERT INTO t1 (first_name, last_name) values ( "blah", "blah");
and then do SELECT * FROM t2;
t2
it says Empty Set (0.00 sec)
for t2
? Shouldn't it at least show the default id
of 1?
t1
:
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| first_name | varchar(20) | NO | | NULL | |
| last_name | varchar(20) | NO | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+------------+------------------+------+-----+---------+----------------+
t2
:
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| address | varchar(50) | NO | | NULL | |
| id | int(10) unsigned | NO | MUL | NULL | |
| last_name | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在关系数据库中,FOREIGN KEY 是一种声明,表示您打算将值插入到 T2 中,该值必须与 T1 中已存在的值相匹配,并且您希望数据库拒绝执行任何会破坏此关系的操作。
这并不意味着数据库将自行创建记录以满足关系。如果您尝试向 T2 中插入 T1 中不存在的值,该命令将失败;它不会将所需的记录添加到 T1。
然而,这与您的建议相反,您希望自动生成外键值。但是,不要求主键值实际上具有引用,而且,主键值可以被引用的次数也没有限制 - 那么数据库如何猜测应该创建什么T2?
也就是说,如果您希望在将数据添加到 T1 时自动执行一些您自己的代码(可以执行您想要的任何操作的代码),您可以在 T1 上创建一个触发器。
In a relational database, a FOREIGN KEY is a declaration that you intend to insert values into T2 that must match an already existing value in T1, and that you want the database to refuse to perform any action that would break this relationship.
It does not mean that the database will create records on its own in order to satisfy a relationship. If you try to insert a value into T2 that does not exist in T1, the command will fail; it will not add the required record to T1.
That is the opposite of what you're suggesting, however, in which you want the foreign key values to get automatically generated. However, there's no requirement that a primary key value actually have references and, furthermore, no limit on the number of times that primary key value can be referenced — so how would the database guess what should be created in T2?
That said, if you want some of your own code to execute automatically when data is added to T1, code which can do whatever you want, you can create a trigger on T1.
不,表不会自动传播。 (但是您可以使用触发器来完成)您必须插入到
t2
中。No, tables won't propagate automatically. (You can however do it with triggers) You will have to insert into
t2
.您可以在表 t1 上创建一个触发器,以便它向 t2 中插入具有正确 id 的行,而其他字段 NULL
外键不会为您插入记录。
注意未经测试的代码
You can create a trigger on table t1 so that it inserts a row into t2 with the correct id and the other fields NULL
Foreign keys will not insert records for you.
NB untested code