MYSQL : How to declare foreign key inline?

Question

I know that is possible to declare primary key inline as

CREATE TABLE `my_table` (
    `id` INTEGER PRIMARY KEY
);

my doubt is if is possible to declare foreign key in the same way, something like

CREATE TABLE `my_table` (
   `foreign_id` INTEGER FOREIGN KEY `other_table` (`other_table_id`)
);

Curhan

What prevented you from simply reading the documentation? Everything you need is there. – Pika

It is a pity that MySQL has not supported this for 20 years, just look at (in chronological order): bugs.mysql.com/bug.php?id=4919, bugs.mysql.com/bug.php?id=17943, bugs.mysql.com/bug.php?id=102904. The task would be rather simple to address, with not much coding involved. Otherwise, give a clear warning that the functionality is still not supposed to work. Do not feed wrong expectations. – Barbate

Answer 1

As of today (Feb. 2020), a code like the following would be executed just fine on e.g. MariaDB (10.3.18-MariaDB-0+deb10u1):

DROP SCHEMA IF EXISTS TABLE_TEST;
CREATE SCHEMA TABLE_TEST;
USE TABLE_TEST;
CREATE TABLE TABLE_TESTA(
    id INT PRIMARY KEY
);

CREATE TABLE TABLE_TESTB(
    fk INT REFERENCES TABLE_TESTA(id)
);

The syntax you would use would be

AttributeName AttributeType REFERENCES TableName(AttributeName)

inside a CREATE statement.

BUT the foreign key would not be created. You can see for yourself by executing

INSERT INTO TABLE_TESTB VALUES (2);

or even

SHOW CREATE TABLE TABLE_TESTB;

which returns

+-------------+--------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------+
| TABLE_TESTB | CREATE TABLE `TABLE_TESTB` (
  `fk` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------------+--------------------------------------------------------------------------------------------------+

So, you can "declare" a foreign key inline, and it will be parsed, but it will not be enforced!

This is a bug that has been documented for a loooooong time but never addressed.

Melanson

Fixed in MariaDB 5.0 (jira.mariadb.org/browse/MDEV-20729), but not in MySQL. – Barbate

Answer 2

From the MySQL documentation:

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.

Bovine

Interesting that they stick to the standard for the CREATE TABLE syntax but drop support when it comes to semantics. – Pika

I believe your answer is outdated. I can't even find your quote in the documentation you are linking. – Tsarism

Answer 3

ALTER TABLE things ADD COLUMN `stuff_id` int(11) DEFAULT NULL, ADD CONSTRAINT `stuff_id_fk` FOREIGN KEY (`stuff_id`) REFERENCES `other_table` (`id`) ON DELETE SET NULL

Safekeeping

The question said inline – Maugre