LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 04-24-2006, 09:55 PM   #1
nephish
Member
 
Registered: Jun 2005
Distribution: arch, ubuntu
Posts: 456

Rep: Reputation: 30
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.
 
Old 04-24-2006, 10:44 PM   #2
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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
 
Old 04-24-2006, 11:39 PM   #3
nephish
Member
 
Registered: Jun 2005
Distribution: arch, ubuntu
Posts: 456

Original Poster
Rep: Reputation: 30
wow, so cool, but i use 4.1 (debian sarge)
rats, but thanks for the info.
 
Old 04-25-2006, 01:20 AM   #4
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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 (
->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.

Last edited by Wim Sturkenboom; 04-25-2006 at 01:26 AM. Reason: broke statements into multiple lines
 
Old 04-25-2006, 08:50 AM   #5
95se
Member
 
Registered: Apr 2002
Location: Windsor, ON, CA
Distribution: Ubuntu
Posts: 740

Rep: Reputation: 32
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?
 
Old 04-28-2006, 02:27 AM   #6
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
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

Last edited by Wim Sturkenboom; 04-28-2006 at 02:30 AM.
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 01:39 PM
MySQL tables from 4.0 to 3.0 michaelbhahn Linux - Software 0 08-31-2004 10:23 AM
MySQL server problem after linking (mysql.sock) ewijaya Linux - General 4 01-19-2004 09:46 AM
mysql tables with a - pk21 Linux - Software 7 05-23-2003 06:27 AM
mysql tables noord28 Linux - Newbie 0 02-20-2003 02:56 AM


All times are GMT -5. The time now is 07:41 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration