More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Functionality Missing from MySQL - Foreign Keys
Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign
key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do
anything. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may
use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a
constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL
only supports these clauses because some applications require them to exist (regardless of whether or not they work).
In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement
to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases
quicker) and much more portable than using foreign keys.
In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table
specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key
constraints for application that can't easily be coded to avoid them.
(Continued on next question...)
