I have the following tables
with the following relational constraintsCode:CREATE TABLE IF NOT EXISTS `cms_channel` ( `id` int(10) NOT NULL AUTO_INCREMENT, `userId` int(10) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `FK_channel_user` (`userId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `cms_channel_category` ( `id` int(10) NOT NULL AUTO_INCREMENT, `parentId` int(10) DEFAULT NULL, `name` varchar(100) NOT NULL, `description` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`parentId`,`name`), KEY `FK_channel_category_channel_category` (`parentId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `cms_channel_category_assignment` ( `categoryId` int(10) NOT NULL, `channelId` int(10) NOT NULL, PRIMARY KEY (`categoryId`,`channelId`), KEY `FK_channel_category_assignment_channel` (`channelId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
As you can see, the relationship between channel_category and channel is of a many to many type.Code:ALTER TABLE `cms_channel_category_assignment` ADD CONSTRAINT `FK_channel_category_assignment_category` FOREIGN KEY (`categoryId`) REFERENCES `cms_channel_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `FK_channel_category_assignment_channel` FOREIGN KEY (`channelId`) REFERENCES `cms_channel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
I want to add a constraint that says a user (channel.userId) can have only 1 channel of the same name (channel.name) in the category (channel_category_assignment.categoryId) to be joined on channel.id=channel_category_assignment.channelId...
Is there a way to set this constraint from mysql or do i need to take care of that through the server side code?



Bookmarks