GIDForums  

Go Back   GIDForums > Computer Programming Forums > MySQL / PHP Forum
User Name
Password
Register FAQ Members List Calendar Search Today's Posts Mark Forums Read
 
 
Thread Tools Search this Thread Rate Thread
  #1  
Old 20-Jul-2004, 03:47
jlee jlee is offline
Awaiting Email Confirmation
 
Join Date: Jul 2004
Posts: 9
jlee is on a distinguished road

foreign keys using phpmyadmin


Hi I am creating a website and linking it to a database using php and mysql. I need to create the databses in phpmyadmin and have decided on the structure of the tables in the database. However, as expected, there are foreign keys in some of the tables - does anyone know how to link the foreign key of one table into another table within phpmyadmin so the database works properly and can be queried using sql? Is it a case of using an sql command to alter the table? I would really appreciate help with this. Thanks
  #2  
Old 20-Jul-2004, 06:04
JdS's Avatar
JdS JdS is offline
Senior Member
 
Join Date: Aug 2001
Location: KUL, Malaysia
Posts: 3,371
JdS will become famous soon enough
Hello jlee,

MySQL has foreign key support for only InnoDB tables at the moment. I don't know if this has changed since the last release I downloaded from the site.

Are the tables you plan to create going to be InnoDB tables or regular MyISAM ones?
  #3  
Old 20-Jul-2004, 08:14
jlee jlee is offline
Awaiting Email Confirmation
 
Join Date: Jul 2004
Posts: 9
jlee is on a distinguished road
Quote:
Originally Posted by JdS
Hello jlee,

MySQL has foreign key support for only InnoDB tables at the moment. I don't know if this has changed since the last release I downloaded from the site.

Are the tables you plan to create going to be InnoDB tables or regular MyISAM ones?

Sorry - I dont know the difference between InnoDB and MyISAM tables. The tables were made in phpmyadmin and are just pretty standard tables.
  #4  
Old 20-Jul-2004, 16:52
JdS's Avatar
JdS JdS is offline
Senior Member
 
Join Date: Aug 2001
Location: KUL, Malaysia
Posts: 3,371
JdS will become famous soon enough
I will assume "...are just prety standard" to mean that your tables are of the type MyISAM. With a MyISAM table, there is NO data integrity check made by MySQL for foreign keys; you can set it up but it's useless.

Examples usually help to explain much better, so here is an example of 3 MyISAM tables and how you normally would insert the primary key from one into another as the 'foreign key'.

Code:
# Create the MEMBER table CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL default '0', `firstname` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM; # Create the DOMAIN table CREATE TABLE `domain` ( `id` int(10) unsigned NOT NULL default '0', `domain_name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM; # Create the MEMBER_SITES table # ----------------------------- # the 2 'foreign keys' here.. CREATE TABLE `member_sites` ( `member_id` int(10) unsigned NOT NULL, `domain_id` int(10) unsigned NOT NULL, PRIMARY KEY (`member_id`, `domain_id` ) ) TYPE=MyISAM;

Looking at the last table (MEMBER_SITES), notice that it is simply a table that references the primary keys of the 2 other tables above it, i.e. MEMBER and DOMAIN.

Since there is NO support for foreign keys in a MySQL MyISAM table at the moment, you have to write your application to check the data intergrity of the MEMBER_SITES table.

In simpler language, everytime your script deletes a record in the `member` table (or `domain` table), the script must DELETE any / all rows in the `member_sites` table that matches MEMBER's id with MEMBER_SITES's member_id i.e. WHERE `member_sites`.`member_id`=`member`.`id`.

With a similar structure for InnoDB tables, you would just delete the record in the MEMBER table and all corresponding MEMBER_SITES records matching the MEMBER's id will be automatically deleted by MySQL if flagged to do so.
  #5  
Old 21-Jul-2004, 03:46
jlee jlee is offline
Awaiting Email Confirmation
 
Join Date: Jul 2004
Posts: 9
jlee is on a distinguished road
Thanks for your help - could you just explain what a MyISAM table is and a InnoDB table? How do you know what type your tables are. I am sure this sounds like a daft question but would be grateful for the answer - thanks
Last edited by JdS : 21-Jul-2004 at 06:01. Reason: removed quote in reply
  #6  
Old 21-Jul-2004, 06:43
JdS's Avatar
JdS JdS is offline
Senior Member
 
Join Date: Aug 2001
Location: KUL, Malaysia
Posts: 3,371
JdS will become famous soon enough
I don't believe that many web hosts enable / support InnoDB tables yet - you may have to check with your web hosts before you even consider the option.

The difference between InnoDB and MyISAM tables are obvious but impossible for me to detail in a reply here...

I decide between using InnoDB tables and MyISAM this way: if the ratio of insert/updates is on par with selects, I usually go with InnoDB tables. So a good example for using InnoDB tables is when I am perhaps developing a website traffic log script or something similar, where every page view by a web surfer will insert or update relevant data to the database very often.

If it's just a table holding articles for example, where you just write once to the database table and millions of people simply read (SELECT) the data via a web page/script, then the obvious option is MyISAM.

Having said that, MyISAM tables are good enough for everything; which explains why they're the default tables.

If there is no indication what the table type is on phpmyadmin, then you can safely assume it's of the type MyISAM. I cannot recall now where exactly this information appears but I'll check on it and add to this reply later. However, I am very certain the option to set the table type is available inside the phpmyadmin script.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
phpMyAdmin error austinstace MySQL / PHP Forum 8 24-Aug-2007 20:42
Keys pressing procceding Ilya C Programming Language 0 13-Sep-2003 10:30

Network Sites: GIDNetwork · GIDApp · GIDBlog · Learning Journal by J de Silva, The

All times are GMT -6. The time now is 03:25.


vBulletin, Copyright © 2000 - 2014, Jelsoft Enterprises Ltd.