kankaro Posted February 4, 2008 Share Posted February 4, 2008 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. Quote Link to comment Share on other sites More sharing options...
mikefrederick Posted February 4, 2008 Share Posted February 4, 2008 what did you just say Quote Link to comment Share on other sites More sharing options...
priti Posted February 4, 2008 Share Posted February 4, 2008 yes you can have 2 foreign keys in your table . what are you trying to acheive make us more clear with dummy example. Regards Quote Link to comment Share on other sites More sharing options...
kankaro Posted February 4, 2008 Author Share Posted February 4, 2008 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. Quote Link to comment Share on other sites More sharing options...
kankaro Posted February 4, 2008 Author Share Posted February 4, 2008 i just created the table with two foreign keys it seems it has a trouble. Quote Link to comment Share on other sites More sharing options...
priti Posted February 4, 2008 Share Posted February 4, 2008 are you using innodb as engine?? Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 4, 2008 Share Posted February 4, 2008 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. Quote Link to comment Share on other sites More sharing options...
kankaro Posted February 6, 2008 Author Share Posted February 6, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.