Steve,
which MySQL version did you use? Both statements work with MySQL-4.1.8 on
Linux.
heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table person (
-> person_id int unsigned not null auto_increment,
-> constraint person_pk primary key (person_id),
-> index(person_id));
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> create table address (
-> address_id int unsigned not null auto_increment,
-> constraint address_pk primary key (address_id),
-> index(address_id));
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> create table person_address (
-> person_id int unsigned not null,
-> address_id int unsigned not null);
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> -- This statement works.
mysql> alter table person_address
-> add constraint person_person_address_FK1
-> foreign key (person_id) references person (person_id);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table person_address
-> add constraint address_person_address_FK1
-> foreign key (address_id) references address (address_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
----- Original Message -----
From: <steven.p.long@stripped>
Newsgroups: mailing.database.myodbc
Sent: Sunday, December 05, 2004 3:44 PM
Subject: Foreign Key Error 1005:150
> --NextPart_Webmail_9m3u9jl4l_6398_1102254139_0
> Content-Type: text/plain
> Content-Transfer-Encoding: 8bit
>
> I am unable to define a foreign key with the following three tables. I am
> unable to find the error having searched the documentation and tried
> several variations.
>
> Note that I created the first two tables with and without the index clause
> in the table ddl with no difference in outcome.
>
> The three tables and the first foreign key, person_person_address_FK1,
> create properly. The second foreign key, address_person_address_FK1,
> causes the error.
>
> Please help.
>
> create table person (
> person_id int unsigned not null auto_increment,
> constraint person_pk primary key (person_id),
> index(person_id));
>
> create table address (
> address_id int unsigned not null auto_increment,
> constraint address_pk primary key (address_id),
> index(address_id));
>
> create table person_address (
> person_id int unsigned not null,
> address_id int unsigned not null);
>
> -- This statement works.
> alter table person_address
> add constraint person_person_address_FK1
> foreign key (person_id) references person (person_id);
>
> -- This statement fails.
> alter table person_address
> add constraint address_person_address_FK1
> foreign key (address_id) references address (address_id);
>
> Replies may be sent to slong@stripped
>
> Thank you!
>
> Steve
> --NextPart_Webmail_9m3u9jl4l_6398_1102254139_0--
