Howdy, I have been using MS Access for 5+ years and am new to MySQL. I understand the theory behind setting relationships between tables, but am trying to get up to speed in how to do it in MySQL.
My environment: Win2k and MySQL 3.23.54
Here's what I want to do: I have two tables, categories and businesses,
and I want to create a relationship between them based on the Node field
(primary key in the categories table, foreign key in the businesses
table). I guess what I fully don't understand yet is the order to do
this: create indexes on the key/foreign key fields and then create
foreign key constraints or vice versa?
Here's a dump of my tables:
Column Information For - saopinions.categories
------------------------------------------------------------------------------------------------------------
Field Type Null Key Default Extra
---------- -------------------- ------ ------ ------- --------------
Node smallint(5) unsigned PRI (NULL) auto_increment
ParentNode smallint(5) unsigned 0
Lineage varchar(100) YES
Category varchar(100)
Depth tinyint(4) unsigned 0
Index Information For - saopinions.categories
------------------------------------------------------------------------------------------------------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality
Sub_part Packed Comment
---------- ---------- -------- ------------ ----------- --------- -----------
-------- ------ -------
categories 0 PRIMARY 1 Node A 62
(NULL) (NULL)
DDL Information For - saopinions.categories
------------------------------------------------------------------------------------------------------------
Table Create Table
----------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
categories CREATE TABLE `categories` (
`Node` smallint(5) unsigned NOT NULL auto_increment,
`ParentNode` smallint(5) unsigned NOT NULL default '0',
`Lineage` varchar(100) default '',
`Category` varchar(100) NOT NULL default '',
`Depth` tinyint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`Node`)
TYPE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 91136 kB'
Column Information For - saopinions.businesses
------------------------------------------------------------------------------------------------------------
Field Type Null Key Default Extra
------- -------------------- ------ ------ ------- --------------
BizID smallint(5) unsigned PRI (NULL) auto_increment
Name varchar(100)
Address varchar(100) YES
City varchar(50)
Zip varchar(10) YES
Phone varchar(20) YES
Fax varchar(20) YES
Web varchar(100) YES
Other varchar(255) YES
Node smallint(5) unsigned MUL 0
Index Information For - saopinions.businesses
------------------------------------------------------------------------------------------------------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality
Sub_part Packed Comment
---------- ---------- -------- ------------ ----------- --------- -----------
-------- ------ -------
businesses 0 PRIMARY 1 BizID A 0
(NULL) (NULL)
businesses 1 Node 1 Node A 0
(NULL) (NULL)
DDL Information For - saopinions.businesses
------------------------------------------------------------------------------------------------------------
Table Create Table
----------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
businesses CREATE TABLE `businesses` (
`BizID` smallint(5) unsigned NOT NULL auto_increment,
`Name` varchar(100) NOT NULL default '',
`Address` varchar(100) default '',
`City` varchar(50) NOT NULL default '',
`Zip` varchar(10) default '',
`Phone` varchar(20) default '',
`Fax` varchar(20) default '',
`Web` varchar(100) default '',
`Other` varchar(255) default '',
`Node` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`BizID`),
KEY `Node` (`Node`)
TYPE=InnoDB
I've tried using a couple of GUI front-ends to manage this but keep
getting errors when setting up the foreign key relationships. Can
someone please point me in the right direction? Many thanks in advance.
--
Chris Montgomery
---------------------------------------------------------------------
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
