question about linking tables in MySQL

nephish · 04-24-2006, 09:55 PM

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.

paulsm4 · 04-24-2006, 10:44 PM

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

nephish · 04-24-2006, 11:39 PM

wow, so cool, but i use 4.1 (debian sarge)
rats, but thanks for the info.

Wim Sturkenboom · 04-25-2006, 01:20 AM

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 (
->orderID bigint unsigned not null primary key auto_increment,
->supplier char(64))
->type=InnoDB;

create table orderitem (
->itemID bigint unsigned not null primary key auto_increment,
->orderID bigint unsigned not null,
->mydata char(64),
->index(orderID),
->foreign key (orderID) references myorder(orderID) on delete cascade)
->type=InnoDB;

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.

95se · 04-25-2006, 08:50 AM

Quote:

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.

Why wouldn't triggers be useful w/ a normalized database?

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?

Wim Sturkenboom · 04-28-2006, 02:27 AM

Quote:

Originally Posted by 95se

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.

Quote:

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 1.9.5.5 of the mysql manual