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.
<http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html>
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
