question about linking tables in MySQL
hello there,
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. |
try using mysql 5.x triggers...
Hi -
It doesn't sound like the tables are properly normalized, or there wouldn't be any *need* to update one table just because another changed. But hey: this *is* MS-Access, after all... Anyway: the feature you're looking for is "triggers". They're a relatively new addition to mySQL (only available in 5.x or above). You can read more about them here: http://www.openwin.org/mike/index.ph...ysql-triggers/ 'Hope that helps .. PSM |
wow, so cool, but i use 4.1 (debian sarge)
rats, but thanks for the info. |
use innodb tables
What you're looking for is on delete cascade and on update cascade. This can only be done when you use innodb tables
Code:
create table myorder ( 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. |
Quote:
Here is a normalized db (table: field, field, ..), categories: categoryid, name posts: postid, categoryid, title, post 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? |
Quote:
If you have the 20 tables linked, it might be another story. However, there is a performance penalty if you use foreign keys. Quote:
|
All times are GMT -5. The time now is 10:55 AM. |