Foreign Keys to AUTO receive referenced Primary Keys in MySQL.. Is it possible?

[snek_one]

Hi there... I just registered here since I found all the answers from the users here to be quite helpful, this seems like a great place to get help (hard to find..)

I have a minor problem with MySQL: I want to be able to construct my database so that the Foreign Key automatically receive the Primary Key value when I add new data to my database. This is for different projects but I'll use my personal CD-database as an example...

Lets say I add a cd to the DB, it will get an ID(int) and according to what type of cd it is (game, movie, app, series) I want to add more information to the appropriate table (game, movie, app, series).

I can figure out how to do this with PHP variables but isn't there a way that I can build my DB in such a way that that won't be necessary? At school I use Sybase PowerBuilder which has this function in a nice GUI :P but they never tought us how to do this in script/sql..

So if someone could help me I'd be very greatful. (Also building a PHP/MySQL intranet site for a company now as a school project, and this would come in handy for that as well..)

[Husain]

Originally posted by snek_one
I can figure out how to do this with PHP variables but isn't there a way that I can build my DB in such a way that that won't be necessary?

I don't think this is possible because MySQL does not support relationships between tables. You will have to make use of a programming language (PHP) to do this.

[snek_one]

Hmm that's too bad.. Guess I'm gonna have to learn more PHP than I thought for this.. Oh well, it's about time I did anyway

Thanks for the help at least!

[DR_LaRRY_PEpPeR]

i believe MySQL 4.1 will support foreign keys properly.

[snek_one]

Alright, that would be about time.. I was already amazed to find out that MySQL supported foreign keys after I upgraded from an old version...

4.1 will probably be out too late for my projects though...

[Maelstrom]

But you could port them over. It may be worthwhile for sheer performance. ...I have a few project I wish I could have put off until this release.

[Hierophant]

Originally posted by DR_LaRRY_PEpPeR
i believe MySQL 4.1 will support foreign keys properly.

About freaking time... I just hope that "properly" includes referential integrity and cascading deletes.

[snek_one]

Aha, is that the expression for what I'm trying to do: referential integrity?

That will save me some time reading manuals in the future

[DR_LaRRY_PEpPeR]

Originally posted by W. Luke
About freaking time... I just hope that "properly" includes referential integrity and cascading deletes.

heh. i think those are included.