在 INSERT 到表 INSERT 连接表中的数据 [英] On INSERT to a table INSERT data in connected tables
问题描述
我有两个表，它们共有一个名为 id_user 的列.这两个表是在某个时候在我的 Drupal 网页中创建的(我不知道，因为我没有创建 Netbeans 项目).
I have two tables that have a column named id_user in common. These two tables are created in my Drupal webpage at some point (that I don't know because I didn't created the Netbeans project).
我在网上查了一下，发现可能通过将 REFERENCES 1sttable (id_user) 添加到第二个表中，它应该复制 1sttable 的值(总是在新用户到达时创建)到 2ndtable 的 id_user 值(我不知道在哪个点创建).正确吗?
I checked on the internet and found that probably by adding REFERENCES 1sttable (id_user) to the second table, it should copy the value of the 1sttable (that is always created when a new user arrives) to the id_user value of the 2ndtable (that I don't know at which point is created). Is it correct?
如果它不正确，我想知道 pgAdmin 中的一种方法可以让我同步这些表，或者至少在同一时刻创建它们.
If it's not correct I would like to know a way in pgAdmin that could make me synchronize those tables, or at least create both of them in the same moment.
我遇到的问题是，新用户一注册就自动在 1sttable 上有一个新行，而要在 2ndtable 上获得一个新行，它需要一些一种激活"，如插入所有数据.我正在寻找一种方法，一旦 1sttable 中有新行，它也会自动在另一个表上创建新行.我不知道如何说得更清楚(英语不是我的母语).
The problem I have is that the new user has a new row on 1sttable automatically as soon as he registers, while to get a new row on 2ndtable it needs some kind of "activation" like inserting all of the data. What I'm looking for is a way that as soon as there is a new row in the 1sttable, it automatically creates the new row on the other table too. I don't know how to make it more clear (English is not my native language).
你给我的解决方案对于这个问题似乎很清楚，但问题有点大:两个表呈现不同类型的变量，应该是它们，一个在mySQL中，带有用户数据(drupal默认对于用户)，那么我在 postgresql 中有 2 个，它们都具有相同的主键(id_user):
The solution you gave me seems clear for the question, but the problem is a little bigger: the two tables presents different kinds of variables, and it should be that they are, one in mySQL, with the user data (drupal default for users), then i have 2 in postgresql, both with the same primary key (id_user):
- 第一个有 118 列，其中大部分是实数；
- 第二个有 50 列，混合类型.
我正在使用的 Web 应用程序需要此列的所有值都不为空(否则我会得到 NullPointerException)才能工作，所以我正在寻找的是(我认为):
the web application i'm using needs both this column with all the values NOT EMPTY (otherwise i get a NullPointerException) to work, so what i'm searching for is (i think):
当用户在 drupal 中注册 - 插入他的电子邮件 - 时，它会自动创建两个已完成的列，以便一旦电子邮件存储在 mysql 中，网络就会自动运行.是否可以?解释清楚了吗?
when the user register -inserting his email- in drupal, automatically it creates the two fulfilled columns, to make the web automatically works as soon as the email is stored in mysql. Is it possible? Is it well explained?
我的环境是:
- windows server 2008 企业版
- 玻璃鱼 2.1
- netbeans 6.7.1
- drupal 6.17
- postgresql 8.4
- mysql 5.1.48
推荐答案
pgAdmin 只是 GUI.你的意思是 PostgreSQL，关系型数据库.
pgAdmin is just the GUI. You mean PostgreSQL, the RDBMS.
A 外键约束，就像您只强制不能使用任何值一样，引用列中不存在该值.您可以使用 ON UPDATE CASCADE 或 ON DELETE CASCADE 来传播引用列的更改，但您不能像描述的那样使用它创建新行.你拿错了工具.
A foreign key constraint, like you have only enforces that no value can be used, that isn't present in the referenced column. You can use ON UPDATE CASCADE or ON DELETE CASCADE to propagate changes from the referenced column, but you cannot create new rows with it like you describe. You got the wrong tool.
您所描述的可以通过触发器来实现.另一种更复杂的方法是 RULE.在这里使用触发器.
What you describe could be achieved with a trigger. Another, more complex way would be a RULE. Go with a trigger here.
在 PostgreSQL 中你需要一个 触发函数，主要使用 <一个 href="https://www.postgresql.org/docs/current/plpgsql.html" rel="nofollow noreferrer">plpgsql 和一个 trigger 在使用它的表上.
In PostgreSQL you need a trigger function, mostly using plpgsql, and a trigger on a table that makes use of it.
类似于:
CREATE OR REPLACE FUNCTION trg_insert_row_in_tbl2()
RETURNS trigger AS
$func$
BEGIN
INSERT INTO tbl2 (my_id, col1)
VALUES (NEW.my_id, NEW.col1) -- more columns?
RETURN NEW; -- doesn't matter much for AFTER trigger
END
$func$ LANGUAGE plpgsql;
在tbl1上触发AFTER INSERT:
CREATE TRIGGER insaft
AFTER INSERT ON tbl1
FOR EACH ROW EXECUTE PROCEDURE trg_insert_row_in_tbl2();
这篇关于在 INSERT 到表 INSERT 连接表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
