Why My Table Doesnt Support Foreign Keys

Foreign keys on create table doesn't get created

MySQL doesn't support column level Foreign key constraint addition to the CREATE syntax.
It has to be table level.

Furthermore, MySQL does not recognize or support “inline REFERENCES
specifications” (as defined in the SQL standard) where the references
are defined as part of the column specification. MySQL accepts
REFERENCES clauses only when specified as part of a separate FOREIGN
KEY specification. For storage engines that do not support foreign
keys (such as MyISAM), MySQL Server parses and ignores foreign key
specifications.

Further reading: here

The reference of the foreign key says the table doesn't exist

What you are trying to do is that you are trying to add self referencing foreign key to the table. You are trying to make your primary key a foreignkey by referencing to same table which is called self referencing foreign key. If your referenced column is unique you should be able to do this since your referencing column (mid) is primary key and it is by default unique and not null.

CREATE TABLE message1(
   mid int not null primary key ,   
   parentMsgId int not null unique,
   title nvarchar (50) not null,
   body nvarchar(50) not null,
   createData image not null,
   fname int ,
   creatorId int not null,
   Foreign Key (fname) references forum1(fname),
   Foreign Key (creatorId) references user1(uid),
   Foreign Key (mid) references message1(parentMsgId)

)

you can have self referencing foreign keys in your table. there is a limit of having such. The columns should have the same data type. Try this code and check the possibility

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

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.

What is the meaning of MyISAM doesn't support foreign key?

For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

from the docs

That means you can define a foreign key but it does not have any effect.

foreign key constraints not enforced on MariaDB, what am I doing wrong?

The problem lies in this tricky behavior that is in fact highlighted in the documentation (emphasis mine):

MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE column definitions, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. Before MariaDB 10.2.1 this was also true for CHECK constraints. Only the syntax for indexes described below creates foreign keys.

In short, one must declare foreign key contraints on separate lines if they are to be enforced (thanks @Honeyboy).

---

Related Topics