# Foreign Key with missing indexes

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

```sql
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:

```sql
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:

```bash
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.

```sql
ALTER TABLE Foo 
MODIFY COLUMN bar_id int DEFAULT NULL;
```

References:

* [Adding Foreign Key inplace\*](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-foreign-key-operations)