helo guys can someone help me!

[kankaro]

hello guys is it possible to have 2 or more foreign key in one table which is that table has a relationship to another table.

[mikefrederick]

what did you just say

[priti]

yes you can have 2 foreign keys in your table . what are you trying to acheive make us more clear with dummy example.

 

Regards

[kankaro]

I supposed to make a web based inventory system, i have a table name products and other table name brand. wat i want is its just a one query in the brand table, when i update or delete some data in the brand table the data in the product table will be deleted also based on the foriegn key in the product table.

 

 

[kankaro]

i just created the table with two foreign keys it seems it has a trouble.

[priti]

are you using innodb as engine??

[gizmola]

Foreign keys -- sure no problem.  If you design it right, then updates to a brand would have no effect, because your normalized design would also use numeric keys to link product and brand together.  In that way, changing for example, the name of a brand would not effect any products that were related to that brand, because the "brandname" column is in the brand table.

 

Now, when you state that in deleting a brand you want to delete all related products, that is known as a cascading delete.  Depending on the database, that can be set up automatically, or may require a trigger.  In MySQL you can set up a cascading delete, assuming a few things, one of which was mentioned by priti:

 

Assuming you're using MySQL:

 

-You need to be using MySQL 5

-You need to have created your tables using the Innodb engine.

-Your table declaration needs to include the ON DELETE CASCADE clause which would have been something like this in your definition of the product table:

 

FOREIGN KEY (brand_id) REFERENCES brand(brand_id)

                      ON DELETE CASCADE

 

If you set this up properly, deleting a brand will delete all products of that brand.  While cascading deletes are convenient for some things, they also represent a significant danger as a user could unknowingly wipe a lot of rows out of a database with one careless delete.  Often you want to control this programatically.  Caveat Emptor.

[kankaro]

thnx all of you guys it now clear in my mind i follow all of your instruction and it works great pretty smooth.

 

.. yup im using an InnoDB engine in my table and my MySQL is version 5.0.45 and PHP version 5.2.4