I have two mysql table.

The first table called register and the second table called user2groups
Register has more columns, the first column is id :

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

The second table (user2groups) has a columnn called id

id INT NOT NULL,

id in second table get the value from first table (id)

An example:

register:

id | username | email
1 zoliky something
2 david


user2groups:

id | group
1 1 <- This is zoliky added to group 1 by default
2 1 <- This is david added to group 1 by default


When i remove "zoliky" user, I remove with:


mysql_query("DELETE FROM register WHERE username='zoliky'");


This query remove this line from register


1 zoliky something


But value 1 remain in user2groups table.

I hear I need to use Innodb or INDEX to remove automaticly the id from user2groups, is true ?

Anyone help me with an example ?

I think you do need to use InnoDB...

If you set up a foreign key from register.id to user2groups.id and use the ON DELETE CASCADE option, it should automatically delete from the "child" table when the "parent" row is deleted.

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

This innodb feature exist in mySQL 4.x ?

Thanks ! :)

I believe so.
Starting from MySQL 3.23.44, InnoDB features foreign key constraints.
MySQL reference manual (http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html)

Yes, for yome reason I do not understand, FOREIGN KEYS (this is the feature you need) are optional in Mysql. From my point of view, a DB makes no sense without them, but ok.

In other words, create your tables like this:


CREATE TABLE register
(
id INT AUTO_INCREMENT,
PRIMARY KEY (id)
) Type=InnoDB;

CREATE TABLE user2group
(
id INT AUTO_INCREMENT,
fk_id INT NOT NULL,
PRIMARY KEY (id),
INDEX idx_fk_id (fk_id),
FOREIGN KEY (fk_id) REFERENCES register(id)
ON DELETE CASCADE
) Type=InnoDB;


Note however, that with this design, one user can only belong to one group. If you want a many-to-may relationship, you need a table in between to connect the groups and the users, see here:

http://www.programmingtalk.com/showthread.php?t=29886

More Info:
http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

Happy Coding!