MySQL Bugs: #19861: [reference_definition] in [column_definition] section of create table statement

Bug #19861	[reference_definition] in [column_definition] section of create table statement
Submitted:	16 May 2006 19:47	Modified:	24 May 2006 10:15
Reporter:	Sergey Boyko	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.22-BK, 5.0.18	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello , 

I will appreciate if someone could explain me 
why the following queries 

============================== 
create table if not exists user_type ( 
id integer not null auto_increment key , 
description varchar (255) not null , 
comments varchar (255) not null 
) Engine=InnoDB charset=utf8; 

create table if not exists user ( 
uid integer not null auto_increment key , 
email varchar (255) not null unique , 
user_type integer not null references user_type(id) 
) Engine=InnoDB charset=utf8; 
============================== 

do not create foreign key from `user` to `user_type` table. 
I assume that's the issue. 

MySQL output follows: 

============================== 
mysql> select @@version; 
+-----------+ 
| @@version | 
+-----------+ 
| 5.0.18 | 
+-----------+ 
1 row in set (0.00 sec) 

mysql> create table if not exists user_type ( 
-> id integer not null auto_increment key , 
-> description varchar (255) not null , 
-> comments varchar (255) not null 
-> ) Engine=InnoDB charset=utf8; 

create table if not exists user ( 
uid integer not null auto_increment key , 
email varchar (255) not null unique , 
user_type integer not null references user_type(id) 
) Engine=InnoDB charset=utf8;Query OK, 0 rows affected (0.03 sec) 

mysql> 
mysql> create table if not exists user ( 
-> uid integer not null auto_increment key , 
-> email varchar (255) not null unique , 
-> user_type integer not null references user_type(id) 
-> ) Engine=InnoDB charset=utf8; 
Query OK, 0 rows affected (0.01 sec) 

mysql> show columns from user; 
+-----------+--------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+-----------+--------------+------+-----+---------+----------------+ 
| uid | int(11) | NO | PRI | NULL | auto_increment | 
| email | varchar(255) | NO | UNI | | | 
| user_type | int(11) | NO | | | | 
+-----------+--------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec) 
============================== 

Here is an equivalent queries set that implement desired functionality. 

============================== 
create table if not exists user_type ( 
id integer not null auto_increment key , 
description varchar (255) not null , 
comments varchar (255) not null 
) Engine=InnoDB charset=utf8; 

create table if not exists user ( 
uid integer not null auto_increment key , 
email varchar (255) not null unique , 
user_type integer not null , 
foreign key (user_type) references user_type(id) 
) Engine=InnoDB charset=utf8; 
============================== 

MySQL output follows: 

============================== 
mysql> create table if not exists user_type ( 
-> id integer not null auto_increment key , 
-> description varchar (255) not null , 
-> comments varchar (255) not null 
-> ) Engine=InnoDB charset=utf8; 

Query OK, 0 rows affected (0.00 sec) 

mysql> 
mysql> create table if not exists user ( 
-> uid integer not null auto_increment key , 
-> email varchar (255) not null unique , 
-> user_type integer not null , 
-> foreign key (user_type) references user_type(id) 
-> ) Engine=InnoDB charset=utf8; 
Query OK, 0 rows affected (0.01 sec) 

mysql> show columns from user; 
+-----------+--------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+-----------+--------------+------+-----+---------+----------------+ 
| uid | int(11) | NO | PRI | NULL | auto_increment | 
| email | varchar(255) | NO | UNI | | | 
| user_type | int(11) | NO | MUL | | | 
+-----------+--------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec) 
==============================

How to repeat:
I want to clarify MySQL server's behaviour in that case

Thank you for a bug report. Verified just as described with 5.0.22-BK on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table if not exists user_type (
    -> id integer not null auto_increment key ,
    -> description varchar (255) not null ,
    -> comments varchar (255) not null
    -> ) Engine=InnoDB charset=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists user (
    -> uid integer not null auto_increment key ,
    -> email varchar (255) not null unique ,
    -> user_type integer not null references user_type(id)
    -> ) Engine=InnoDB charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table user_type\G
*************************** 1. row ***************************
       Table: user_type
Create Table: CREATE TABLE `user_type` (
  `id` int(11) NOT NULL auto_increment,
  `description` varchar(255) NOT NULL,
  `comments` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `uid` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `user_type` int(11) NOT NULL,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

So, REFERENCES (unlike FOREIGN KEY) is just ignored for InnoDB tables. It is either a bug or, at least, a documentation request (this "feature", if intended, should be described in http://dev.mysql.com/doc/refman/5.0/en/create-table.html). Now that manual page contains REFERENCE keyword only in 2 places, and none of them explains that it is ignored.

Duplicate of http://bugs.mysql.com/bug.php?id=13301