This is my first post. I hate to be asking for help right out of the starting gate but I have hit a wall.

I am in the process of creating what I hope will be a well designed relational database. It is MySQL and the tables are INNODB. 15 tables in all.

Example:

3 tables: Event, Participant, Address

User enters all data on form.

The Event table is the main table.

How do I get the PK from one table entered as a FK in another?

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?

Thanks

if you are using auto_incrtement columns, you would do this using the LAST_INSERT_ID() function
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.

-PatP

also note that foreign key relationships are only enforced in the InnoDB table type and not the default MyISAM table type.

You can declare them in the latter but you can insert incorrect values in the secondary tables and your CASCADING deletes and updates won't work.

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.

-PatP

pat, have you never seen an application which requires just one, albeit large, table? so that you need the speed of an indexed search rather than a flat file? myisam is perfect for that

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.

-PatP

Thanks for the help. Sorry for my delay in responding - been away.

The tables are INNDB. I will get a little practice in and see how it goes.