How do I get the PK from one table entered as a FK in another?
if you are using auto_incrtement columns, you would do this using the LAST_INSERT_ID() function
Originally Posted by mtbadfish
Do I want FK's from Participant (part_id)and Address (add_id) added to the Event table?
Or do I want the Event (event_id) fk added to the other tables?
it does get confusing sometimes
okay, here's a rule of thumb that should help you
if an event can have multiple participants, but a participant have only one event, then event-to-participant is a one-to-many relationship, and the foreign key goes into the "many" table, the participant
if a participant can have multiple events, but an event have only one participant, then participant-to-event is a one-to-many relationship, and the foreign key goes into the "many" table, the event
if an event can have multiple participants, and a participant have multiple events, then event-to-participant is a many-to-many relationship, and you need a 3rd "relational" table between them
Are you asking how to move the value of a PK from a parent table, and putting that value into an FK column in the child table, or are you asking how do you delcare the relationship between the PK column and the FK column? I can't tell which question you are asking, and they get rather different answers.
As far as the values goes, you take the value from the PK and insert it into the FK, just as R937 has suggested. If you are using auto_increment then the LAST_INSERTED_ID() function will help here.
If you are looking for how to declare the relationship, check the MySQL manual for CREATE TABLE and pay special attention to the FOREIGN KEY declaration.
That's a good point about MyISAM. While I understand that MyISAM is an important feature of MySQL, the fact that it allows a user to declare referential integrity, but then won't enforce that integrity is something I have a hard time swallowing. I consider that to be an unforgivable omission, one that keeps me from considering MySQL as a relational database engine.
You can certainly implement a relational database using MySQL, and if you use InnoDB, you can have a relational database engine, but if you use MyISAM then you are "on your own" for ensuring basic relational integrity.
Oh I agree. There are many applications that MyISAM is perfectly adequate for, and for those applications it is a fine tool. It is fast, simple, and has a lot to recommend it.
You can develop a relational application to run against text files, and MyISAM will support that relational application better than a text file will. MyISAM is still not able to provide full support of what I consider the basic requirements for a relational database (relational integrity) even though you can certainly run a relational application against a MyISAM data source.