Mysql – Basics of Foreign Keys in MySQL

foreign-keysmysql

Is there any good explanation of how to use MySQL's foreign key construct?

I don't quite get it from the MySQL docs themselves. Up until now I've been handling things like foreign keys with joins and programming code.

And the second part of the question, are there any improvements to be made by using MySQL's inbuilt foreign keys?

Best Solution

FOREIGN KEYS just ensure your data are consistent.

They do not improve queries in sense of efficiency, they just make some wrong queries fail.

If you have a relationship like this:

CREATE TABLE department (id INT NOT NULL)
CREATE TABLE employee (id INT NOT NULL, dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES department(id))

, then you cannot delete a department if it has some employee's.

If you supply ON DELETE CASCADE to the FOREIGN KEY definition, the referencing rows will be deleted automatically along with the referenced ones.

As a constraint, FOREIGN KEY actually slows down the queries a little.

Extra checking needs to be performed when deleting from a referenced table or inserting into a referencing one.

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Mysql – How to see all foreign keys to a table or column

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

Best Solution

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

Mysql – How to see all foreign keys to a table or column

Related Question