Re: onDelete cascade after table has been created

In article <5623d727-428f-4202-b4b8-403a7e619c9c_at_googlegroups.com>,  <apoorv.kanungo_at_gmail.com> wrote:
> On Saturday, February 20, 2016 at 8:48:21 PM UTC+5:30, Tony Mountifield wrote:
> > In article <afdf9d11-31be-4173-ac9c-1adc22f2fef9_at_googlegroups.com>,
> > <apoorv.kanungo_at_gmail.com> wrote:
> > > Hello I know we can use cascading while creating table but After I created table and there are bunch of data in it how
> > > would I do ondelete cascade.
> > >
> > > I have a user table and a article table with one to many relationship and I have a foreign key user_id in article which
> > > refrences id in user table when I ran this query
> > >
> > > ALTER TABLE `article`
> > > FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE;
> > >
> > > Now i Have foreign key but when I delete an entry in user table the corresponding entry in article does not get deleted.
> >
> > First thing would be to check the table definitions:
> >
> > SHOW CREATE TABLE `article`;
> > SHOW CREATE TABLE `user`;
> >
> > and make sure the foreign key definition really exists.
> >
> > Also check what the ENGINE is for those tables. If the engine is MyISAM,
> > then foreign key definitions are accepted but not acted upon. You need
> > the ENGINE to be InnoDB:
> >
> > ALTER TABLE `article` ENGINE=InnoDB;
> > ALTER TABLE `user` ENGINE=InnoDB;
> >
> > Cheers
> > Tony
> > --
> > Tony Mountifield
> > Work: tony_at_softins.co.uk - http://www.softins.co.uk
> > Play: tony_at_mountifield.org - http://tony.mountifield.org
>
> Hello Foreign Key does exist I can see in the structure tab the little key thing beside user_id I changed storage engine to innodb
> If I do describe article under user_id key has value mul
> under indexes it has following value
> user_id BTREE No No user_id 1 A No
> Even If I HAVE a user with an id=4 and artcle in article table with user_id=4 if I delete user the entry in article table does not get deleted.
>

If the foreign key definitions were created when the tables were MyISAM, and you then change the tables to InnoDB, it may be that you need to drop and re-create the foreign keys in order for them to be recognised properly. I don't know, but try it.

Otherwise, post the DDL for both `article` and `user`.

Regards
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org