MySQL link tables - view, foreign keys or trigger?

I apologize if "link" is not the exact term for this in advance.

I have a table with user info, (user&password). I need the same user:password p开发者_开发百科air to appear in another table in the same database. the second table needs to copy the first one at all times - if an entry is deleted from the first one, or added - the changes must apply to the second table instantly.

Please, I need an example of how this can be done, as I do not know the terminology to search on google..

I only need two columns to be a duplicate, not the entire table.

A view may work, depending on the constraints imposed by your existing system, and those imposed by views. It saves duplicating data:

CREATE TABLE web_users (username VARCHAR(255), password VARCHAR(255));

CREATE VIEW forum_users AS SELECT username, password FROM web_users;

INSERT INTO web_users VALUES ('user1', 'password1');

SELECT * FROM forum_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |
+----------+-----------+

INSERT INTO forum_users VALUES ('user2', 'password2');

SELECT * FROM forum_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |
| user2    | password2 |
+----------+-----------+

SELECT * FROM web_users;
+----------+-----------+
| username | password  |
+----------+-----------+
| user1    | password1 |
| user2    | password2 |
+----------+-----------+

This can be accomplished with triggers.

The real question is why would you want to have duplicate information in the database.

The term in mysql is called foreign key.

You would need a innodb storage engine for this to work

details: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

However using natural key as foreign key has some drawbacks,
this question has been discussed before: How to choose my primary key?

An effective way to implement such a requirement is via a database trigger.

use storage engine as INNODB in mysql and do the indexing in it