Page 1 of 1
How can I add relations and restrictions to existing tables?
Posted: Thu Apr 02, 2009 1:12 pm
by JellyFish
I've tried adding:
Code: Select all
ALTER TABLE `a` ADD FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE RESTRICT
But is seems every time I delete a row from `categories` when there is a related row in `a` the ON DELETE RESTRICT part does
not kick in and prevent the row from being removed.
What I'm trying to accomplish is a restriction on a table. I'd like for the `categories` table's rows to not be deleted if there are rows in the `a` table referencing the row in `categories` being deleted.
I don't get exactly what I'm doing wrong. :S
Please help me on this one.
Thanks for reading.
Re: How can I add relations and restrictions to existing tables?
Posted: Fri Apr 03, 2009 2:23 am
by VladSun
[sql]CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FK_category_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `IX_category_id` (`FK_category_id`), FOREIGN KEY (`FK_category_id`) REFERENCES `category` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/sql]
Both tables must be InnoDB
Re: How can I add relations and restrictions to existing tables?
Posted: Fri Apr 03, 2009 6:51 pm
by JellyFish
VladSun wrote:[sql]CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FK_category_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `IX_category_id` (`FK_category_id`), FOREIGN KEY (`FK_category_id`) REFERENCES `category` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/sql]
Both tables must be InnoDB
I don't know if both of my tables are InnoDB or not. How do I get the engine type from a table? Is there a way to change my existing tables to that InnoDB, if they aren't already?
Re: How can I add relations and restrictions to existing tables?
Posted: Mon Apr 06, 2009 12:43 pm
by JellyFish
I figured out how to change a table's engine:
Code: Select all
ALTER TABLE `table_name` ENGINE = InnoDB
I changed both of my tables to InnoDB and ran my last query in my OP. Everything is working great, but I still have some questions.
What are some of the advantages of one engine over another? Why does one engine have this foreign key constraint functionality and another engine doesn't. What functionality does MyISAM have over InnoDB; what would I be losing if I go with InnoDB?
Also, can I ever change a foreign key constraint? If I wanted to change the ON DELETE clause of a foreign key constraint, would I have to create a new constraint and delete the old one?