MySQL Bugs: #39144: CREATE TABLE undocumented behaviour of reference

Bug #39144	CREATE TABLE undocumented behaviour of reference_definition options
Submitted:	31 Aug 2008 9:49	Modified:	6 Oct 2008 21:59
Reporter:	Jeremy Gardiner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.22, 5.0.66a	OS:	Windows
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
Here's what it says in the documentation:

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

In other words, there are three options, MATCH before ON DELETE before ON UPDATE (where zero, 1, 2 or 3 options may be present).

Now here's the actual behaviour:

reference_definition:
    REFERENCES tbl_name (index_col_name,...) [option ...]

option:
MATCH FULL | MATCH PARTIAL | MATCH SIMPLE | ON DELETE reference_option | ON UPDATE reference_option

In other words, you can supply as many options as you like, possibly repeated, and in any order.

Of course, any statement which is correct "as documented" works OK.

How to repeat:
mysql> CREATE TABLE a1 (c1 INT REFERENCES b1 (c1,c2) ON UPDATE RESTRICT MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT MATCH PARTIAL);
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE IF NOT EXISTS a1 (c1 INT, FOREIGN KEY (c1) REFERENCES b1 (c1,c2) ON UPDATE RESTRICT MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT MATCH PARTIAL);
Query OK, 0 rows affected, 1 warning (0.00 sec)

Suggested fix:
If this is intended behaviour, update the manual. If not, update parser behaviour to match the manual.

Verified with 5.0.66a:

C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> create table b1 (c1 int, c2 int, primary key(c1, c2));
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE a1 (c1 INT REFERENCES b1 (c1,c2) ON UPDATE RESTRICT MATCH SIMPLE ON
    -> UPDATE CASCADE ON DELETE RESTRICT MATCH PARTIAL);
Query OK, 0 rows affected (0.14 sec)

I think this is a reasonable documentation request.

Either http://dev.mysql.com/doc/refman/5.0/en/create-table.html or parser should be changed.

See related Bug#34455 Ambiguous foreign keys syntax is accepted

This is a bug in the parser, which accepts illegal syntaxes.

No change in the manual is needed.

The parser code has been fixed to only accept legal syntaxes.
The fix itself was implemented by Bug#34455, closing this report as a duplicate.

Thanks for the bug report.