Foreign Key with missing indexes

Relational databases have references to other tables via 1-Many relationships using a foreign key.

CREATE TABLE Bar(
    id          int NOT NULL,
    name        varchar(128),
    PRIMARY KEY (id)
);

CREATE TABLE Foo (
    id          int NOT NULL,
    name        varchar(128),
    bar_id      int NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (bar_id) REFERENCES Bar(id)
);

However there’s nothing to stop you from dropping an index or not having one. Removing the referential constraint compromises data integrity. Foo records can now exist with invalid bar_id's

To add the constraint in-place, you need to run the following:

SET FOREIGN_KEY_CHECKS = 0; 
SELECT @@FOREIGN_KEY_CHECKS; 
ALTER TABLE Foo 
ADD CONSTRAINT foo_bar_id FOREIGN KEY (bar_id) 
REFERENCES Bar(id); 
SET FOREIGN_KEY_CHECKS = 0; 
SELECT @@FOREIGN_KEY_CHECKS;

Now when querying by the foreign key you should see much faster queries which will use the index:

mysql> explain select count(*) from Foo where bar_id = %;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+----------------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | Foo    | NULL       | ref  | foo_bar_id    | foo_bar_id |       5 | const |   23 |   100.00 | Using index |
+----+-------------+--------+------------+------+--------------------------------------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

There are times when you need to allow for NULL values but also maintain consistency. You then insert records with NULL references to bar_id by default if not specified. However if specified, they should exist.

ALTER TABLE Foo 
MODIFY COLUMN bar_id int DEFAULT NULL;

References:

Adding Foreign Key inplace*

Foreign Key with missing indexes

Foreign Key with missing indexes

Comments

More from this blog

8 Simple reminders

Design a url shortner

No regrets

Design a distributed unique id generator

luck surface area

Command Palette

Comments

More from this blog