The only table type at this moment that supports foreign keys is innodb.
it is distributed with mysql 4.0.
However you need to take the source files or the tar file to instal, the
rpm's have an older version of innodb.
On creation of the table you have to define your constraints (cf manual at
www.innodb.com)
For the moment it is nog possible to do it with an alter table after the
table is created.
----- Original Message -----
From: "McGrotty, Charles" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 26, 2001 3:10 PM
Subject: Making Foreign Keys within MySQL?
> I realized I made my previous post look like it was a reply and not the
> original question, so here I go again... =o)
>
> Original message below
> =========================================================================
>
> Hello,
>
> I have tables in a user administration database and need to link different
> tables with FK's for integrity.
>
> How do I do this?
>
> for example:
>
> I have two tables, one called applications, the other called
> forum_moderators.
>
> Applications Catches all form submissions.
> Forum_Moderators hold the details of active/current moderators
>
> I want to link these two tables by rsm_id in applications, and fk_rsm_id
in
> forum_mod's.
>
> How would i do this in MySQL and or PHP?
>
>
> Table Structure:
> for those that need a clearer picture of my layout, I have supplied the
SQL
> statement to create the tables
>
> #--------------------------------------------------------
> #
> # Table structure for table 'applications'
> #
>
> DROP TABLE IF EXISTS applications;
>
> CREATE TABLE `applications` (
> `rsm_id` tinyint(4) NOT NULL auto_increment,
> `first_name` varchar(20) NOT NULL default '',
> `last_name` varchar(20) NOT NULL default '',
> `city` varchar(10) NOT NULL default '',
> `state` char(2) NOT NULL default '',
> `dob` varchar(10) NOT NULL default '',
> `forum_nick` varchar(20) NOT NULL default '',
> `forum_select1` varchar(30) NOT NULL default '',
> `forum_select2` varchar(30) NOT NULL default '',
> `forum_select3` varchar(30) NOT NULL default '',
> `bio` blob NOT NULL,
> `email_addr` varchar(100) NOT NULL default '',
> `processed` set('0','1') NOT NULL default '',
> `add_date` date NOT NULL default '0000-00-00',
> `accept_denied` set('A','D') NOT NULL default '',
> `acc_dec_date` date NOT NULL default '0000-00-00',
> PRIMARY KEY (`rsm_id`),
> UNIQUE KEY `email_addr` (`email_addr`),
> UNIQUE KEY `forum_nick` (`forum_nick`),
> UNIQUE KEY `rsm_id` (`rsm_id`),
> KEY `rsm_id_2` (`rsm_id`),
> KEY `forum_nick_2` (`forum_nick`),
> KEY `email_addr_2` (`email_addr`)
> #--------------------------------------------------------
>
> #
> # Table structure for table 'forum_moderators'
> #
>
> DROP TABLE IF EXISTS forum_moderators;
> CREATE TABLE `forum_moderators` (
> `formod_id` tinyint(4) NOT NULL auto_increment,
> `name` varchar(40) NOT NULL default '',
> `city` varchar(10) NOT NULL default '',
> `state` char(2) NOT NULL default '',
> `forum_nick` varchar(20) NOT NULL default '',
> `mod_forum_1` varchar(30) NOT NULL default '',
> `mod_forum_2` varchar(30) NOT NULL default '',
> `mod_forum_3` varchar(30) NOT NULL default '',
> `email_addr` varchar(100) NOT NULL default '',
> `tremination_date` date default NULL,
> `fk_rsm_id` tinyint(4) NOT NULL default '0',
> PRIMARY KEY (`formod_id`),
> UNIQUE KEY `formod_id` (`formod_id`,`forum_nick`),
> KEY `formod_id_2` (`formod_id`)
> ) TYPE=MyISAM;
> #--------------------------------------------------------
> #--------------------------------------------------------
>
> Regards,
> Charlie
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
