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. Steve -------------- Original message -------------- > Something is wrong, but it's hard to say what. It seems unlikely you entered > 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 >
