Foreign Key

[rahulroy]

Hello,

Can we use Foreign Key in MySQL as we do in case of MS-SQL DB Server? If yes what is the syntax to create this relation between two tables?

If no, what alternatives do we have?

Thanks.

[ahundiak]

The latest versions have some support for foriegn key constraints.
http://www.mysql.com/doc/en/CREATE_TABLE.html

n MySQL Version 3.23.44 or later, InnoDB tables support checking of foreign key constraints. See section 7.5 InnoDB Tables. Note that the FOREIGN KEY syntax in InnoDB is more restricted than the syntax presented above. InnoDB does not allow index_name to be specified, and the columns of the referenced table always have to be explicitly named. Starting from 4.0.8 InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. See the InnoDB manual section for the precise syntax. See section 7.5 InnoDB Tables. For other table types, MySQL Server does parse the FOREIGN KEY, CHECK, and REFERENCES syntax in CREATE TABLE commands, but without further action being taken. See section 1.8.4.5 Foreign Keys.

Since most of us are not using the latest mysql releases, we have to implement foriegn key constraints at the client level. In other words, if you delete a master record then you must also explicitly delete all the linked records.