ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
i am trying to reproduce a data server that a company uses to present information to its customers via the web. No problem. What they are using right now is Microsoft Access, connecting via ODBC to a server on line. The cool thing that they can do is have several tables linked together. Like if a certain field is updated in one table, it updates the same info automatically in another table. So, i want to reproduce this in MySQL, but i can't seem to find out how. Is there a way to pull this off on the server side ? or does that have to be done on the client side ?
thanks for any tips.
Above we've created 2 tables, a parent table (myorder) and a child table (orderitem). For both we have specifically specified that they should be of type innodb as the default is type MyIsam.
In the child table we have created the foreign key column (orderID bigint ...), created an index for that foreign key (index ....), indicate that it is the foreign key (foreign key ...), what it references and what to do on delete.
Last edited by Wim Sturkenboom; 04-25-2006 at 01:26 AM.
Reason: broke statements into multiple lines
Now, wouldn't it make sense that if you deleted a category, it would go through and delete all the posts associated w/ that category as well?
I think that that will be the case usually. But in your example it only requires two SQL statements to achieve the same (or one if you combine them).
If you have the 20 tables linked, it might be another story.
However, there is a performance penalty if you use foreign keys.
Foreign key enforcement offers several benefits to database developers:
• Assuming proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.
• Centralized checking of constraints by the database server makes it unnecessary to perform these checks on the application side. This eliminates the possibility that different applications may not all check the constraints in the same way.
• Using cascading updates and deletes can simplify the application code.
• Properly designed foreign key rules aid in documenting relationships between tables.
Do keep in mind that these benefits come at the cost of additional overhead for the database server to perform the necessary checks. Additional checking by the server affects performance, which for some applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial applications have coded the foreign key logic at the application level for this reason.)
Source: paragraph 22.214.171.124 of the mysql manual
Last edited by Wim Sturkenboom; 04-28-2006 at 02:30 AM.