MySQL Bugs: #11188: 15.7.4 - FOREIGN KEY Constraints describes old behaviour

Bug #11188	15.7.4 - FOREIGN KEY Constraints describes old behaviour
Submitted:	8 Jun 2005 23:01	Modified:	9 Jun 2005 12:53
Reporter:	Martin Maisey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.12a	OS:	Windows (WinXP Pro)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
Note that the comment in the manual section 15.7.4

"In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order."

appears to be out of date. In MySQL 4.1.12a on Windows at least, the index appears to be automatically created if it does not already exist. This is different from 4.0.24, where an errno 150 resulted.

Also, I scanned (albeit quickly) through the changelists and couldn't find a reference to this behaviour change.

I've added a comment to the page.

How to repeat:
N/A

Suggested fix:
Amend the documentation to describe the new behaviour

Clarified synopsis

Are you using: SET FOREIGN_KEY_CHECKS = 0; ? Or you can provide
the script for to repeat that behavior. Thanks.

D:\dbs\4112\bin>mysqladmin -uroot create db7

D:\dbs\4112\bin>mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.12-nt

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

mysql> CREATE TABLE child(id INT, parent_id INT,
    ->                    INDEX par_ind (parent_id),
    ->                    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                      ON DELETE CASCADE
    -> ) TYPE=INNODB;
ERROR 1005 (HY000): Can't create table '.\db7\child.frm' (errno: 150)
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE child(id INT, parent_id INT,
    ->                    INDEX par_ind (parent_id),
    ->                    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ->                      ON DELETE CASCADE
    -> ) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.09 sec)

Sorry, there was an error in my original submissions. It should have read 

"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order."

note *referencing*, not referenced. The rest of the defect still stands.

Script to replicate is below. This failed on 4.0 MySQL, as it was unable to create the PRINCIPAL_PERMISSION.PERMISSION_ID foreign key constraint - this was as per the docs. 

If I run it on 4.1, MySQL seems to automatically create the required PERMISSION_ID index as it creates the constraint, and the script succeeds.

---

# -----------------------------------------------------------------------
# SECURITY_PRINCIPAL
# -----------------------------------------------------------------------
drop table if exists SECURITY_PRINCIPAL;

CREATE TABLE SECURITY_PRINCIPAL
(
		            PRINCIPAL_ID INTEGER NOT NULL,
		            CLASSNAME VARCHAR (254) NOT NULL,
		            IS_MAPPING_ONLY BIT NOT NULL,
		            IS_ENABLED BIT NOT NULL,
		            FULL_PATH VARCHAR (254) NOT NULL,
		            CREATION_DATE TIMESTAMP NOT NULL,
		            MODIFIED_DATE TIMESTAMP NOT NULL,
    PRIMARY KEY(PRINCIPAL_ID)
);

# -----------------------------------------------------------------------
# SECURITY_PERMISSION
# -----------------------------------------------------------------------
drop table if exists SECURITY_PERMISSION;

CREATE TABLE SECURITY_PERMISSION
(
		            PERMISSION_ID INTEGER NOT NULL,
		            CLASSNAME VARCHAR (254) NOT NULL,
		            NAME VARCHAR (254) NOT NULL,
		            ACTIONS VARCHAR (254) NOT NULL,
		            CREATION_DATE TIMESTAMP NOT NULL,
		            MODIFIED_DATE TIMESTAMP NOT NULL,
    PRIMARY KEY(PERMISSION_ID)
);

# -----------------------------------------------------------------------
# PRINCIPAL_PERMISSION
# -----------------------------------------------------------------------
drop table if exists PRINCIPAL_PERMISSION;

CREATE TABLE PRINCIPAL_PERMISSION
(
		            PRINCIPAL_ID INTEGER NOT NULL,
		            PERMISSION_ID INTEGER NOT NULL,
    PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
    FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID)
    ,
    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID)
    
);
---

And no, I'm not using SET FOREIGN_KEY_CHECKS = 0;

Hi!

MySQL-4.1 automatically creates an index on the referencing columns. But it does NOT automatically create an index on the referenced columns. The referenced columns are in a different table. It would be difficult to add an index there automatically.

The referenced columns typically are the primary key of the referenced table. Therefore, there normally exists an index on them, anyway.

Regards,

Heikki

Thanks Heikki - yes, this is the behaviour I'm seeing. It makes absolute sense and is definitely better than the 4.0 error. 

However, this (definitely non-critical!) bug was raised on the documentation, not the server itself. The docs state in the second bullet point describing required conditions to create a foreign key constraint:

"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order."

The third bullet makes exactly the same point around the referenced table. This seems to imply that the behaviour is identical in both cases, which, as of 4.1, it no longer is.

It would be great if the second bullet could be removed, but some additional text added below noting the new 4.1 index autocreation behaviour on the referencing table.

Additionally, I couldn't see a changelist entry for this change - on the other hand it was only a quick scan of the changelists and it's entirely possible I missed it.

Martin, Marko,

sorry, closed the bug too quickly!

The manual is out-of-date.

Marko, please add to this bullet:

"In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. 
"

that MySQL creates the index on the referencing table automatically, starting from 4.1.2.

Please also update the change notes of 4.1.2.

Regards,

Heikki

The InnoDB news-4.1.2 already mentions the change. I fixed the manual section "InnoDB foreign key constraints".