foreign key possible in myisam?

i know that i can use foreign key constraints in InnoDB, but my isp doesn't offer this type of db.  Is it possible to use foreign key in MYISAM?  If so how would i go about implementing this?
liennyAsked:
Who is Participating?
 
snoyes_jwConnect With a Mentor Commented:
Well, foreign keys just ensure that a key exists before inserting/updating, and deletes children rows if the parent row is deleted.

So say you have tables tableA and tableB, and tableB has a foregin key on tableA.
tableA:
id

tableB:
id
AId

Before you insert or update into tableB, check tableA:
SELECT * FROM tableA WHERE id = potential_AId
If no rows are returned, don't do the insert/update.  Alternatively, you can do it like this:
INSERT INTO tableB (AId, other_field) SELECT id, other_literal_string FROM tableA WHERE id = potential_AId;

When deleting from tableA, do this:
DELETE FROM tableB WHERE AId = id_to_delete;
DELETE FROM tableA WHERE id = id_to_delete;
0
 
akshah123Commented:
I don;t think so

on following page

http://dev.mysql.com/doc/mysql/en/ANSI_diff_Foreign_Keys.html

it clearlly says

" At a later stage, foreign key constraints will be implemented for MyISAM tables as well. "

THus no foreign keys in MYISAM right now.

: (
0
 
liennyAuthor Commented:
so i would have to enforce my foreign key in my sql statements?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
snoyes_jwCommented:
yes
0
 
liennyAuthor Commented:
can you give me an example on how i would do that, then I'll reward the points.
Thanks!
0
 
liennyAuthor Commented:
lets say i have tables:

employees:
id
empNo
firstName
lastName

training:
id
eid
trainDate
training

when i do an insert how would i grab that id value from the employees table and have it automatically insert into the eid field of the training table?
0
 
liennyAuthor Commented:
sorry...let me clarify...it'll be 2 seperate forms...they can enter the employees in one form and then have a link to enter the training for that employee
0
 
snoyes_jwCommented:
I assume the id is automatically generated using auto_increment?

You have a couple of choices.
1) If using PHP, use the mysql_insert_id() method
http://us3.php.net/manual/en/function.mysql-insert-id.php

2) Use MySQL's LAST_INSERT_ID() method
http://dev.mysql.com/doc/mysql/en/Information_functions.html

3) Structure your second query to select from the employee table:
INSERT INTO training (eid, trainDate, training) SELECT id, '2004-08-11', 'GMP Training' FROM employees WHERE empNo = 42;
0
 
liennyAuthor Commented:
thanks.
0
All Courses

From novice to tech pro — start learning today.