Sorry for another email. But this is just to expand on what SHawn said..I could also have created an index and then referenced the column.
So without a primary key. I can create the parent , then create the child and the index . mysql> create TABLE parent ( id int(16) , name varchar(128))ENGINE=Innodb; mysql>create TABLE child ( id int(16) , name varchar(128), parent_id int(16))ENGINE=innodb; mysql> create index parent_id_fk ON parent (id); And now the foreign key constraint works: mysql> ALTER TABLE child ADD CONSTRAINT child_parent_id_fk FOREIGN KEY parent_id_fk (parent_id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 Thanks for your prompt help. Hari On Tue, Feb 22, 2011 at 2:13 PM, hari jayaram <hari...@gmail.com> wrote: > 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 >> > >
