I have two questions about REFERENCES:
1. It appears that mySQL treats REFERENCES associated with an
attribute differently than FOREIGN KEY (<blah>) REFERENCES...
Specifically, the first form does not appear to work, while the later
does. Here's a simple example (slightly edited):
mysql> CREATE TABLE person (id char(5) primary key);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE phone (id char(5) not null REFERENCES person(id),
num char(10) not null, PRIMARY KEY (id, num));
Query OK, 0 rows affected (0.06 sec)
mysql> show create table phone;
| phone | CREATE TABLE `phone` (
`id` char(5) NOT NULL default '',
`num` char(10) NOT NULL default '',
PRIMARY KEY (`id`,`num`)
) TYPE=InnoDB CHARSET=latin1 |
1 row in set (0.00 sec)
mysql> insert into phone value ('foo', 'bar');
Query OK, 1 row affected (0.04 sec)
It appears that the constraint is not created and is certainly not
enforced. Now consider what should be the same phone table.
mysql> drop table phone;
Query OK, 0 rows affected (0.29 sec)
mysql> CREATE TABLE phone (id char(5) not null, num char(10) not null,
PRIMARY KEY (id, num), FOREIGN KEY (id) REFERENCES person(id));
Query OK, 0 rows affected (0.09 sec)
mysql> show create table phone;
| Table | Create Table
| phone | CREATE TABLE `phone` (
`id` char(5) NOT NULL default '',
`num` char(10) NOT NULL default '',
PRIMARY KEY (`id`,`num`),
FOREIGN KEY (`id`) REFERENCES `person` (`id`)
) TYPE=InnoDB CHARSET=latin1 |
1 row in set (0.00 sec)
mysql> insert into phone values ('foo', 'bar');
ERROR 1216: Cannot add or update a child row: a foreign key constraint
fails
Why are these different?
2. In the manual (and in several posts), it clearly states (see
Section 7.5.4.2 of the 4.1.0-alpha manual)
"Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns"
However, I did not create an indicies, but it worked. What's up?
Thanks in advance