Steve,
----- Original Message ----- From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, December 06, 2004 1:00 PM
Subject: Re: Foreign Key Error 1005:150
--NextPart_Webmail_9m3u9jl4l_14802_1102330771_0 Content-Type: text/plain Content-Transfer-Encoding: 8bit
Michael,
Thank you for your reply. Here is a bit more info. I changed the default table type to innodn in the my.ini file before creating the database, so all tables are innodb. I tried the create statements with and without explicit index clauses with all permutations - same result each time. I agree that something is wrong. Did you try running the ddl you suggested below? If so, did it work for you?
I downloaded the latest release from thr ANL mirror which says v 4.1.2 in the file name. When I run MySQL, the system says it is 4.0.22. Is the engine version different than the release version? This is a secondaary issue however.
if the server says it is 4.0.22, then you are running 4.0.22.
Please post the COMPLETE output of the mysql client when you try the problematic command sequence, and after that run SHOW INNODB STATUS. It prints a detailed description of the latest FOREIGN KEY error.
Since you are running 4.0.22, MySQL does not automatically create indexes on FOREIGN KEYs, and your command sequence is indeed expected to fail.
Steve
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 --------------
Something is wrong, but it's hard to say what. It seems unlikely you entered--NextPart_Webmail_9m3u9jl4l_14802_1102330771_0--
exactly those commands and got an error only on the last ALTER TABLE.
First, you need InnoDB tables to support foreign keys, but you don't specify
the table engine in your CREATE statements. The default is MyISAM, unless
you've changed it. But that's not it. If they were MyISAM tables, neither
ALTER would work, but if they're all InnoDB, then all should work. Is it
possible that just table address is MyISAM?
In order to create a foreign key, you must have an index on the columns on
each side of the relationship. That is, you need person_id and address_id
to be indexed in both tables. Prior to 4.1.2, you had to do that by hand,
but in 4.1.2 and later it's automatic. Again, all or nothing, so not likely
relevant here.
Some other things to note (which are unrelated to the error):
There is no need to put an index on a column which has already been indexed
as the primary key. It's a waste of space that adds overhead to inserts.
You are relying on MySQL to create indexes for you in table person_address,
but I don't think it will make the best choices in this case. You need an
index on each column, but you most likely also need the combination of
person_id and address_id to be unique. In other words, if you let mysql
create indexes for you to satisfy the foreign key needs, you get separate
single-column indexes, but you need a combined column unique constraint
which renders one of the single column indexes redundant.
How about:
CREATE TABLE person ( person_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint person_pk PRIMARY KEY (person_id) ) ENGINE=InnoDB;
CREATE TABLE address ( address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint address_pk PRIMARY KEY (address_id) ) ENGINE=InnoDB;
CREATE TABLE person_address ( person_id INT UNSIGNED NOT NULL, address_id INT UNSIGNED NOT NULL, CONSTRAINT person_address_pk PRIMARY KEY (person_id, address_id), INDEX (address_id), CONSTRAINT person_person_address_FK1 FOREIGN KEY (person_id) REFERENCES person (person_id); CONSTRAINT address_person_address_FK1 FOREIGN KEY (address_id) REFERENCES address (address_id); ) ENGINE=InnoDB;
Michael
[EMAIL PROTECTED] wrote:
> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
