Thanks shawn for your reply. Your simplification of the innodb status message and this post which I just read (http://lists.mysql.com/mysql/221900 ) tells me what I am doing wrong.
I need the referenced column to be indexed. I guess one way of ensuring that is to declare it as a primary key . So when I changed my example to do this: mysql> create TABLE parent ( id int(16) , name varchar(128), primary key (id))ENGINE=Innodb; mysql> create TABLE child ( id int(16) , name varchar(128), parent_id int(16))ENGINE=Innodb; mysql> ALTER TABLE child ADD CONSTRAINT child_parent_id_fk FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 And everything works Hari On Tue, Feb 22, 2011 at 2:02 PM, Shawn Green (MySQL) < shawn.l.gr...@oracle.com> wrote: > Hello Hari, > > You already posted the best answer we could provide :) > > > On 2/22/2011 13:00, hari jayaram wrote: > >> Hi I am getting a Foreign key error . >> ... >> >> I have attached the create table syntax for both the parent and child >> tables >> and the innodb status below. ... >> mysql> show innodb status; >> >> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > >> | Status... >> | >> > > ------------------------ >> LATEST FOREIGN KEY ERROR >> ------------------------ >> 110222 12:54:53 Error in foreign key constraint of table >> bioscreencast_lap/#sql-1515_130f: >> FOREIGN KEY (id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO >> ACTION: >> Cannot find an index in the referenced table where the >> referenced columns appear as the first columns, or column types >> in the table and the referenced table do not match for constraint. >> Note that the internal storage type of ENUM and SET changed in >> tables created with>= InnoDB-4.1.12, and such columns in old tables >> cannot be referenced by such columns in new tables. >> See >> http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html >> for correct foreign key definition. >> > > To rephrase, a little: Columns must be indexed before they can participate > in Foreign Keys. > > See the link you provided for more details. > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN >
