On INSERT to a table INSERT data in connected tables

Question

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).

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?

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.

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).

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):

• the first has 118 columns, most of them real integer;
• the second has 50 columns, with mixed types.

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):

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?

My environment is:

• windows server 2008 enterprise edition
• glassfish 2.1
• netbeans 6.7.1
• drupal 6.17
• postgresql 8.4
• mysql 5.1.48

Answer 1

pgAdmin is just the GUI. You mean PostgreSQL, the RDBMS.

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.

What you describe could be achieved with a trigger. Another, more complex way would be a RULE. Go with a trigger here.

In PostgreSQL you need a trigger function, mostly using plpgsql, and a trigger on a table that makes use of it.

Something like:

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;

And a trigger AFTER INSERT on tbl1:

CREATE TRIGGER insaft
AFTER INSERT ON tbl1
FOR EACH ROW EXECUTE PROCEDURE trg_insert_row_in_tbl2();

Answer 2

You might want to read about using Drupal hooks to add extra code to be run when a user is registered. Once you know how to use hooks, you can write code (in a module) to insert a corresponding record in the 2nd table. A good candidate hook to use here would be hook_user for Drupal 6 or hook_user_insert for Drupal 7.

The REFERENCES you read about is part of an SQL command to define a foreign key constraint from the second table to the first. This is not strictly necessary to solve your problem, but it can help in keeping your database consistent. I suggest you read up on database structures and constraints if you want to learn more on this topic.