Basics of Foreign Keys in MySQL?

by

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.

More Related Contents:

  1. MySQL Creating tables with Foreign Keys giving errno: 150
  2. MySQL Error 1215: Cannot add foreign key constraint
  3. Mysql error 1452 – Cannot add or update a child row: a foreign key constraint fails
  4. MySQL Cannot Add Foreign Key Constraint
  5. How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
  6. Required to join 2 tables with their FKs in a 3rd table
  7. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  8. Can a foreign key reference a non-unique index?
  9. MySQL – Conditional Foreign Key Constraints
  10. How do I use on delete cascade in mysql?
  11. MySql Error 150 – Foreign keys
  12. Add Foreign Key to existing table
  13. How to change the foreign key referential action? (behavior)
  14. How to create relationships in MySQL
  15. How to truncate a foreign key constrained table?
  16. MySQL Removing Some Foreign keys
  17. Force InnoDB to recheck foreign keys on a table/tables?
  18. Is it possible to reference one column as multiple foreign keys?
  19. How to insert values in table with foreign key using MySQL?
  20. Show constraints on tables command
  21. Meaning of “n:m” and “1:n” in database design
  22. MySQL Errno 150
  23. How to use django models with foreign keys in different DBs?
  24. How to remove constraints from my MySQL table?
  25. Foreign key constraints: When to use ON UPDATE and ON DELETE
  26. MySQL DROP all tables, ignoring foreign keys
  27. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  28. MySQL errorno 121
  29. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  30. MySQL Relationships

Leave a Comment