MySQL does NOT enforce foriegn key relationships, at least with MyISAM tables, which are the default. Other tables types might be different.
Relationships are used by Access to make the query constructor work more easily, but are pretty irrelevant to actual use of the DB. They do provide an index on that column, which is important, so make sure you do that manually with MySQL: create table person ( personID int not null auto_increment primary key, name varchar(50), ... ); create table car ( carID int not null auto_increment primary key, personID int not null, make varchar(50), ... foreign key fk_person references person(personID), key k_personID (personID) ); The foreign key line in the 'car' table will be ignored by MySQL. The line below it will construct an index on the personID column to facilitate fast searching. Note that I didn't use the first column as the foreign key. The biggest thing that FKs provide (in my experience) is the ability to easily do cascading deletes (deleting a person automaticlaly deletes all his/her cars). That type of thing will have to be done manually with MySQL, while SQL Server (and perhaps Access) can be configured to do that for you. barneyb > -----Original Message----- > From: dan martin [mailto:[EMAIL PROTECTED] > Sent: Monday, March 17, 2003 8:50 AM > To: CF-Talk > Subject: sql2k vs mySQL vs postgresql (for win2k) > > > How do you folks handle the access relationships when changing to > mySQL? From the manual it looks like the equivalent functionality > is covered using foreign key constraints. Is this right? > > The foreign key constraints require that both sides of the > relationship have the foreign key be the first record of the > table. How do you do this when one table has relationships with > multiple tables? Only one foreign key can be the first, so it > sounds like each table can only have one foreign key constraint. > Is this right? > > Thanks for your help. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
