Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
5.5.33a
-
Suse Linux 13.1
Description
Inconsistent behavior in CREATE TABLE statements: When foreign key statements are listed directly behind attributes they do not get a constraint while the get one if the foreign keys statements are listed separately after all attributes. The following examples will demonstrate this:
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 1
|
Server version: 5.5.33-MariaDB openSUSE package
|
|
|
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
|
|
|
MariaDB [(none)]> create database testdb;
|
Query OK, 1 row affected (0.00 sec)
|
|
|
MariaDB [(none)]> use testdb;
|
Database changed
|
MariaDB [testdb]> create table t(id int primary key);
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [testdb]> show create table t;
|
+-------+-------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
| t | CREATE TABLE `t` (
|
`id` int(11) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [testdb]> create table t2(id int, t_id int, foreign key (t_id) references t(id));
|
Query OK, 0 rows affected (0.02 sec)
|
|
|
MariaDB [testdb]> show create table t2;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`id` int(11) DEFAULT NULL,
|
`t_id` int(11) DEFAULT NULL,
|
KEY `t_id` (`t_id`),
|
CONSTRAINT `t2b_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `t` (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [testdb]> create table t3(id int, t_id int references t(id));
|
Query OK, 0 rows affected (0.02 sec)
|
|
|
MariaDB [testdb]> show create table t3;
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| t3 | CREATE TABLE `t3` (
|
`id` int(11) DEFAULT NULL,
|
`t_id` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [testdb]>
|