Ed, InnoDB does not support the 'short' syntax:
> CREATE TABLE phone (id char(5) not null > REFERENCES person(id), > num char(10) not null, PRIMARY KEY (id, num)); It simply ignores that REFERENCES definition. It is in the TODO list to add support for that syntax, too. Until then you have to use the 'long' syntax: > 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 Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL ----- Original Message ----- From: "Ed Smith" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, September 26, 2003 1:39 AM Subject: REFERENCES question in mySQL 4.1 - Take 2 > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
