Re: Foreign Key Error 1005:150

Michael Stassen Sun, 05 Dec 2004 22:03:30 -0800

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

[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]

Re: Foreign Key Error 1005:150

Reply via email to