Steve,
which MySQL version did you use? Both statements work with MySQL-4.1.8 on Linux.
[EMAIL PROTECTED]:~/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: <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED]
Thank you!
Steve
--NextPart_Webmail_9m3u9jl4l_6398_1102254139_0--
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
