MySQL won't let me add a foreign key to table. There is no error message

Question

I use this query:

ALTER TABLE recent_article_entry ADD FOREIGN KEY (`article`) REFERENCES article(`id`);

Or this:

ALTER TABLE recent_article_entry ADD CONSTRAINT fk_rae_article FOREIGN KEY (article) REFERENCES article(id);

And it says No errors; 1 row affected, taking 127 ms.

Which row was affected?

If I go to information_schema.KEY_COLUMN_USAGE, I don't see my newly generated foreign key there. And if I try to add to recent_article_entry, constraints are not enforced.

I'm very confused. I tried to debug the query by changing some of the values to jibberish.

So:

ALTER TABLE sldakjfalksdjf ADD FOREIGN KEY (`article`) REFERENCES article(`id`); 

fails as expected, and

ALTER TABLE recent_article_entry ADD FOREIGN KEY (`aslkdjfalksjdf`) REFERENCES article(`id`);

fails as expected.

But for whatever reason,

ALTER TABLE recent_article_entry ADD FOREIGN KEY (`article`) REFERENCES aslkdjfdkf(`id`); 

succeeds fine (as well as changing the referenced column to jibberish). I've made plenty of foreign keys before. Why is MySQL suddenly ignoring my queries and not creating the foreign key?

---

Edit At the request of ray, here is the output of the describe statements:

describe recent_article_entry:

Field        |Type       |Null|Key|Default|Extra
-------------|-----------|----|---|-------|-----
article      |varchar(55)|NO  |PRI|       |
file_location|varchar(55)|NO  |PRI|       |

describe article

Field         |Type          |Null |Key|Default          |Extra
--------------|--------------|-----|---|-----------------|-----
id            |varchar(55)   |NO   |PRI|                 |
title         |varchar(255)  |YES  |   |NULL             |
author        |varchar(55)   |NO   |MUL|                 |
translator    |varchar(55)   |NO   |MUL|                 |
source        |varchar(1000) |YES  |MUL|0                |
published_flag|tinyint(1)    |NO   |   |                 |
published     |timestamp     |YES  |   |CURRENT_TIMESTAMP|
short_title   |varchar(55)   |YES  |   |NULL             |

describe file_location

Field |Type       |Null |Key|Default          |Extra
------|-----------|-----|---|-----------------|-----
id    |varchar(55)|NO   |PRI|                 |
views |int(11)    |YES  |   |NULL             |

And the version is 5.1.56-log

Answer 1

Possible Cause - Collations and Encodings

I've often seen MySQL not be informative enough when trying to create FK constraints between tables. One particular instance where I got burned years ago was the table collation and/or string encodings for the column in question. They have to match in both tables. In other words, you should verify that if your VARCHAR PK column in some table A is using latin1 encoding, then the VARCHAR FK column in some table B must also be using latin1 enconding and not something else, like utf8.

In this next section, I reproduce your basic setup, but I made sure that the table collation and column encodings were the same. In my case, they're default, but you should check your own database tables to be sure.

Possible Cause - Storage Engine

In older version of MySQL (you're using 5.1, I'm using 5.7) the default storage engine is MyISAM. This storage engine is not ACID-compliant. Therefore, it does not support foreign key constraints. You must make sure your storage engine is set to InnoDB for each table.

You can specify this as the default in your MySQL config file or as part of the create table SQL statement. Currently, this one seems a bit more likely because you say your query is not producing error messages. That being said, I don't immediately recall if the previous possibility produces messages or remains silent.

In the next section I show that this is working normally, at least for me, including the query you've provided.

Creating Tables

I've tried to reproduce some of the basics for your tables, enough to get PKs and FKs setup between them. They're reproduced below, including the create statements:

mysql> create table `test`.`article` (
    `id` varchar(55) not null,
    `title` varchar(255) null default null,
    primary key (`id`)
);
Query OK, 0 rows affected (0.42 sec)

mysql> create table `test`.`file_location` (
    `id` varchar(55) not null,
    `views` int(11) null default null,
    primary key (`id`)
);
Query OK, 0 rows affected (0.35 sec)

mysql> create table `test`.`recent_article_entry` (
    `article` varchar(55) not null,
    `file_location` varchar(55) not null,
    primary key (`article`, `file_location`)
);
Query OK, 0 rows affected (0.32 sec)

The tables are described below:

mysql> show tables;
+----------------------+
| Tables_in_test       |
+----------------------+
| article              |
| file_location        |
| recent_article_entry |
+----------------------+
3 rows in set (0.00 sec)

mysql> describe article;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(55)  | NO   | PRI | NULL    |       |
| title | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe file_location;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(55) | NO   | PRI | NULL    |       |
| views | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe recent_article_entry;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| article       | varchar(55) | NO   | PRI | NULL    |       |
| file_location | varchar(55) | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Creating Constraints

Here we check the column usage prior to the foreign keys being added and note that they don't show up because they don't yet exist:

mysql> select TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE;
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| TABLE_NAME           | COLUMN_NAME   | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| article              | id            | NULL                    | NULL                  | NULL                   |
| file_location        | id            | NULL                    | NULL                  | NULL                   |
| recent_article_entry | article       | NULL                    | NULL                  | NULL                   |
| recent_article_entry | file_location | NULL                    | NULL                  | NULL                   |
+----------------------+---------------+-------------------------+-----------------------+------------------------+
4 rows in set (0.00 sec)

I went ahead and created the FKs with the following query in my test database:

alter table `test`.`recent_article_entry`
    add constraint `fk_recent_article_entry_article_id`
        foreign key (`article`)
        references `test`.`article` (`id`)
        on delete restrict
        on update cascade,

    add constraint `fk_recent_article_entry_file_location`
        foreign key (`file_location`)
        references `test`.`file_location` (`id`)
        on delete restrict
        on update cascade;

I then checked the information_schema as I had done before. You can now see the constraints listed:

mysql> select TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE;
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| TABLE_NAME           | COLUMN_NAME   | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| article              | id            | NULL                    | NULL                  | NULL                   |
| file_location        | id            | NULL                    | NULL                  | NULL                   |
| recent_article_entry | article       | NULL                    | NULL                  | NULL                   |
| recent_article_entry | file_location | NULL                    | NULL                  | NULL                   |
| recent_article_entry | article       | test                    | article               | id                     |
| recent_article_entry | file_location | test                    | file_location         | id                     |
+----------------------+---------------+-------------------------+-----------------------+------------------------+
6 rows in set (0.00 sec)

I know my query looks different compared to yours, but yours works fine for me too, as I'll show next. I dropped the FKs shown above and then gave your own query a try:

mysql> alter table recent_article_entry add foreign key (`article`) references article(`id`);
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE;
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| TABLE_NAME           | COLUMN_NAME   | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+----------------------+---------------+-------------------------+-----------------------+------------------------+
| article              | id            | NULL                    | NULL                  | NULL                   |
| file_location        | id            | NULL                    | NULL                  | NULL                   |
| recent_article_entry | article       | NULL                    | NULL                  | NULL                   |
| recent_article_entry | file_location | NULL                    | NULL                  | NULL                   |
| recent_article_entry | article       | test                    | article               | id                     |
+----------------------+---------------+-------------------------+-----------------------+------------------------+

As you can see, your own query worked fine for me. Let me know in the comments if you have additional questions or spot something you think I might've missed.