Keys

Anon

http://www.mysql.com/doc/e/x/example-Foreign_keys.html is where I found documentation on creating tables with relationships in mysql. After the tables are created, when I use describe table2;, it doesn't show that it references anything in the "extra" column. Should it? If so, can someone give me the command to create a field that references another table?

gushy

MySQL does not support foreign keys. It parses the Foreign Key clause in create table statements so that you can port SQL code from other databases.

If you have tables that have linked data, you need to perform the joined selection manually.

Foreign Keys are dangerous, if you delete a record out of one table it could delte all related data by way of the foreign key (and you might not want it too). Likewise relying on foreign keys can bring your whole system to a halt because you forgot one small dependancy in your 200 table database. Also (so I've read - I don't use FKs so wouldn't know) foreign keys impact on server performance.

So my advice? If you feel you need to use Foreign Keys then choose a database that supports them. I think you could live without them, and living without them will turn you into a better SQL coder.

-- Nick Gushlow

Anon

Ok. So I did do it manually.. but it still isn't showing any proof taht I did. I used these steps, http://www.mysql.com/doc/e/x/example-Foreign_keys.html
I tried this simple join and when I do a "describe table1;" command, it doesn't comment about the reference I put in. Shouldn't it say that somewhere?

gushy

No because all the foreign key check does is make sure that the table/column you reference exists. Describe table1 does just that. If you wish to see the structure of table2 you have to explicitly describe it.

Other than that simple check the foreign key is not used, therefore it is not displayed because it's not important.
If it did display it, you'd be tempted to use it and then you'd be left wondering why your queries don't work.

Basically you have to remember which data is held where and why. All this shold be documented in your ERDs when you did your system design.

I can see how this might seem confusing and a lot of work when you have come from a database that does use foreign keys. However once you've used it for a bit you get used to it and it becomes second nature.

-- Nick Gushlow