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
>
> steven.p.long@stripped 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 slong@stripped
> >
> > Thank you!
> >
> > Steve
>
