adding foreign key to table

[forgodsake8]

Hi,
How do i go about doing this in mysql, Ive checked on the internet but I can't find the syntax for it!!
Thanks.

[raf]

http://www.mysql.com/doc/en/CREATE_INDEX.html

note: you can't 'create' a foreign key. The "primary key-foreign key" is just a relationship-type that is commenly used in db-design.
You can set up a primary key (a unique index, usualy an auto_num), but each regular index can be a foreign key --> when it contains the values to join with the primary key of another table, then we call it a foreign key.

[Kiwi]

Standard SQL has a recognisable foreign key syntax. When you specify a field as a foreign key (primary key from another table), it will check that any values entered in that table are valid foreign keys from other tables (there are added benefits, such as cascading deletes and updates). Its main purpose is to enforce the referential integrity (thus is actually quite important for implementing a relational database at the implementation level, rather than just at the conceptual level).

mySql doesn't enforce the foreign key syntax in its standard tables (myISAM). You can use the standard SQL, but if you try and enter and illegal value, then it won't stop you (the most common error is deleting a record in a child table that is still referred to in the parent; foreign keys can also prevent this from happening -- that's the magic of cascade rules). If you use the InnoDB table type, then you will have enforced foreign keys. It should be noted that most of MySql's select/insert speed stems from the fact it doesn't enforce foreign keys. When running InnoDB tables, the MySql benchmarks end up pretty close to compaparable database servers such as Oracle.

You can create a foreign key with the CREATE TABLE syntax or add one to an existing table with ALTER TABLE. If you are not using InnoDB tables, this will have no effect (but won't return an error, either). I think you can also change the table type with ALTER TABLE, but I may be wrong.

Here is a page about Foreign Keys in mySql, including the syntax. Here's a search of all references to foreign keys in the mySql documenation.

[raf]

Your right.

Seems that mySQL indeed supports foreign keys in InnoDB tables .

[Kiwi]

I'm not sure what would happen if you defined foreign keys in standard myISAM tables, then changed the tables to InnoDB. In theory, the new tables should have foreign keys, but I don't know how the parsing would work out. The docs say something about myISAM parsing the foreign key command, but not implementing it (which could just mean it's read and ignored). I would also exect problems if you had illegal foreign keys in myISAM tables, then tried to change them to InnoDB and enforce the foreign keys. There might be something in the user notes, or it can be tested easily enough.

[Yusogga]

I used FK in myIsam table, didn't improve anything.
Unless you use a PMA_Relation table (like in PHP_myAdmin), is there really any need to add defined FK in myIsam?

[raf]

Like Kiwi explained, and like you can see inside the mySQL docs, FK's doesn't do anything in a myISAM tables so their is n point definign them. If you need them to do cascades or enforce dataintegrity, then you need to change your tablatypes.

[Kiwi]

The only point of using foreign key syntax with myISAM tables is to allow you to use standard SQL to define the tables. If, for example, you are creating an application to work on different databases and you don't really need foreign keys in mySql (but may as well use them in other DBs, where the additional over-heads are going to be incurred anyway), then you don't need to change the SQL that you're sending. They will have no functional effect on myISAM tables. It's important to note that while you can use mixed table types in a single mySql database -- but for foreign keys to work, both tables must be InnoDB.