Re: onDelete cascade after table has been created

On Monday, February 22, 2016 at 4:45:55 PM UTC+5:30, Tony Mountifield wrote:
> 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

Muchas gracias Tony It Worked I dropped the foreign key and then created it. For anyone having problem follow this steps  change storage engine to innodb drop foreign key then create it again.  it worked like a charm.
Thnaks a lot everybody for responding. Received on Mon Feb 22 2016 - 13:58:28 CET