minte9
LearnRemember   ANTIPATTERNS





Antipattern

Skipping foreign key Many developers avoid foreign key constraints. The constraints make it inconvenient to update related columns in multiple table.

Delete

For example, if you need to delete a row that other rows depend on. You have to delete the child rows first, to avoid violating foreign key constraints. But this problem is solvable. 
 
DELETE FROM BugStatus WHERE status = 'BOGUS'; # errors
DELETE FROM Bugs WHERE status = 'BOGUS';
DELETE FROM BugStatus WHERE status = 'BOGUS'; # success

Update

The unsolvable problem is when you update a column that child rows depend on. You can’t update the child rows before you update the parent. And you can’t update the parent before you update the child values that reference it. It’s a catch-22 scenario. 
 
UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS'; # errors
UPDATE Bugs SET status = 'INVALID' WHERE status = 'BOGUS'; # success

Code

Some developers find these scenarios difficult to manage. So they decide not to use foreign keys at all. Skipping foreign key constraints means that it's your responsibility to write some code. That code ensure referential integrity manually.

Solution

Add Constrains 
 
CREATE TABLE Bugs (
    reported_by BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
    FOREIGN KEY (status) REFERENCES BugStatus(status)
);
Don't search for and correct data integrity mistakes. You can prevent these mistakes from entering your database in the first place. The software industry average is 15 to 50 bugs per 1000 lines of code. All other things being equal, if you have fewer lines of code, you have fewer bugs.

Cascading

FK have another feature you can’t mimic using application code, cascading updates. 
 
CREATE TABLE Bugs (
    reported_by BIGINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
    FOREIGN KEY (status) REFERENCES BugStatus(status)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT
);
This solution allows you to update or delete the parent. It lets the database takes care of any child rows that reference it. Updates to BugStatus and Accounts propagate automatically to child rows in Bugs. There’s no longer a catch-22 problem. RESTRICT for the FK reported_by means that you can’t delete an account. You can not delete it if some rows in Bugs reference it. The constraint blocks the delete and raises an error. It’s true that foreign key constraints have a bit of overhead. But compared to the alternative, foreign keys prove to be a lot more efficient.





Questions and answers:
Clink on Option to Answer




1. Many developers avoid foreign key constrains because ...

  • a) updating related columns in multiple tables is slow
  • b) they can't update a column that child rows depend on

2. Foreign keys are used ...

  • a) for corecting data integrity mistakes
  • b) to prevent mistakes for entering in database

3. ON UPDATE CASCADE means ...

  • a) when you update the parent DB takes care of any child
  • b) updating the parent don't update the childs

4. ON DELETE RESTRICT means ...

  • a) you can’t delete a row if in another table some rows reference it
  • b) when you delete a row it doesn't delete the reference

5. You can't use code script for ...

  • a) periodicaly checking database integrity
  • b) cascading updates

6. Which storage engine doesn't support foreign-keys?

  • a) MyISAM
  • b) INNODB


References: