First, my apologies. I originally posted this to the
mailing.databases.mysql newsgroup, not realizing that
the newsgroup is just mirror of the mailing list.
I have since figured out the answer to my second
question (I think) - see below. My answer is: The
Primary Key specification creates an index that the
foreign key uses.
I STILL have my first question - see below:
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
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
