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