This DDL doesn't do what was requested. There is no foreign key.
mysql> create table a(i int primary key);This DDL doesn't do what was requested. The desc option is ignored.
mysql> create table b(i int primary key, ai int, index aix(ai));
mysql> alter table b add constraint foreign key (ai) references a;
mysql> create table c(i int, o int, index x(i asc, o desc));Surprise! This query can use the index: select * from c order by i asc, o asc
mysql> insert into c values (1,1), (2,2), (3,3);
mysql> analyze table c;
mysql> explain select * from c order by i asc, o asc;Surprise! This query cannot use the index: select * from c order by i asc, o desc
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | c | index | NULL | x | 10 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
mysql> explain select * from c order by i asc, o desc;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | c | index | NULL | x | 10 | NULL | 3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
Posts
Not to mention "inline" foreign keys, which is valid SQL and allowed according to the parser, but ignored by everyone, even when poperly specifying the InnoDB engine. I.e.:
ReplyDeleteCREATE TABLE t2(c1 INT REFERECNES t1(c1)) ENGINE=InnoDB:
does NOT create a foreign key in the table, whereas:
CREATE TABLE t2(c1 INT, FOREIGN KEY(c1) REFERENCES t1(c1)) ENGINE=InnoDB;
WILL create a FOREIGN KEY! Both of these statement are equal, according to the SQL standard, and both are accepted by the MySQL parser, with no errors, but in MySQL achieve two different results! (this is an old, known deficiency).
This comment has been removed by the author.
ReplyDelete+1 for that. I totally agree with you there. It allows for rampant assumptions and probably, in some cases, tears.
ReplyDeleteAlso CHECK constraint declarations are parsed but ignored by all MySQL storage engines. No error, no warning, nothing. Just no constraint.
ReplyDeleteI've found this to be an annoying "feature" of MySQL for many years. The attitude seems to be it's ok not to error out when encountering non-supported features. That never made any sense to me, I thought that was part of the parser's job, to tell me when I've done something wrong.
ReplyDeleteI already added options to the parser to optionally disable the use of triggers, stored procedures, XA, subqueries and views. Now I will add a few more to raise errors on FK creation for tables other than InnoDB and the use of DESC during index creation.
ReplyDeleteFor more on 'parsed but ignored' see http://search.mysql.com/search?site=refman-50&q=%22parsed+but+ignored%22
ReplyDeleteFor the bug on DESC with CREATE index see http://bugs.mysql.com/bug.php?id=13375