Hi to all...
  I'm trying to create a PHP game online just for the fun of it (Giving me a reason to learn php!)

  A lot of my table will require a many to many link.  Here a small example of my table :

|          Table User      | --------------------------- | Id   |  Name | stuff...| |        Table Object    | -------------------------- | Id  |  Name | stuff...|

Now I want to create a link between both. But many Object may have a User, and a User may have many Object.

Right Now, This is what I have :

CREATE TABLE User_Object ( User_Id INT NOT NULL, Objet_Id INT NOT NULL, PRIMARY KEY(User_Id, Object_Id) );

Now... Is there a way in MySQL to make sure that if I delete a Object, All reference to this object in User_Object will be delete, and the same with a user, Or do I have to create this on my own?




____________________________
A Stick give a wise man something to think about... and a fool, something to put in is mouth.

You have to create it using different queries...

when you delete a object:

DELETE FROM Objects WHEN ID=ObjectIDToDelete

and also all its references

DELETE FROM User_Object WHERE Object_Id=ObjectIDToDelete

When you delete a user:

DELETE FROM Users WHEN ID=UserIDToDelete

and also all its references

DELETE FROM User_Object WHERE User_Id=UserIDToDelete

____________________________
AndreaVB

I think I've found what I was serching for, it's the cascading command. I first need to have my many-to-many table use a Foreign key, then use the CASCADE command : Syntax from the MySQL reference book :

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,                       price DECIMAL,                       PRIMARY KEY(category, id)) TYPE=INNODB; CREATE TABLE customer (id INT NOT NULL,                       PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,                       product_category INT NOT NULL,                       product_id INT NOT NULL,                       customer_id INT NOT NULL,                       PRIMARY KEY(no),                       INDEX (product_category, product_id),                       FOREIGN KEY (product_category, product_id)                         REFERENCES product(category, id)                         ON UPDATE CASCADE ON DELETE RESTRICT,                       INDEX (customer_id),                       FOREIGN KEY (customer_id)                         REFERENCES customer(id)) TYPE=INNODB;

____________________________
A Stick give a wise man something to think about... and a fool, something to put in is mouth.