How can I add relations and restrictions to existing tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

How can I add relations and restrictions to existing tables?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How can I add relations and restrictions to existing tables?

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: How can I add relations and restrictions to existing tables?

Post 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?
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: How can I add relations and restrictions to existing tables?

Post 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?
Post Reply