== Quote from TheKeith (ksdump@gmail.com)'s article
> On Jun 18, 12:12 pm, TheKeith <ksd...@gmail.com> wrote:
> > On Jun 18, 11:23 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> >
> >
> >
> > > On 18 Jun, 15:50, TheKeith <ksd...@gmail.com> wrote:
> >
> > > > > Have a read through this:
> >
> > > > >http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
> >
> > > > Hey thanks! So I've followed all the instructions, but for some reason
> > > > I'm getting a:
> >
> > > > ERROR 1005 (HY000): Can't create table './keithsdb/
> > > > kmfnlistens.frm' (errno: 150)
> >
> > > > when creating the referencing table, with this syntax:
> >
> > > > create table kmfnListens (listener varchar(20) not null, kmfnSongID
> > > > bigint not null auto_increment, index (kmfnSongID), foreign key
> > > > (kmfnSongID) references kmfnSongs(songID) on delete cascade on update
> > > > cascade) engine=innodb;
> >
> > > > I tried creating the table without the foreign key clause, and it
> > > > works. But I can't get the foreign key to work. After creating the
> > > > table without it, I issued a:
> >
> > > > alter table kmfnListens foreign key kmfnSongID references
> > > > kmfnSongs(songID) on delete cascade on update cascade;
> >
> > > > but got an error 1064 syntax error--what am I doing wrong? The
> > > > referenced table.column is kmfnSongs.songID and that column is its
> > > > primary key, so I'm stumped! Help! thanks.
> >
> > > According to your first post:
> > > "One of the primary key columns is of DATETIME type and the other is a
> > > SMALLINT AUTO_INCREMENT type"
> > > But in the above you have "kmfnSongID bigint not null auto_increment".
> >
> > > The joining fields must be identical.
> >
> > Ah yeah, I neglected to mention that I changed teh structure of the
> > referenced tables so that each one only has one primary key column--
> > because I wasn't able to convert that table to innoDB as it was with
> > the two-column primary key, one of which was auto-increment.
> AHH! -- I got it working finally. I'm not sure what I did right this
> time though.
from the error code you gave, it appears that the key constraint (foreign key) was
incorrectly formed just like captain p mentioned if they are not of the same kind,
it won't work.
--
POST BY: lark with PHP News Reader