I have the following tables
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` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`categoryId` int(10) NOT NULL,
`channelId` int(10) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `categoryId` (`categoryId`,`channelId`),
KEY `FK_channel_category_assignment_channel` (`channelId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
with the following relational constraints
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;
As you can see, the relationship between channel_category and channel is of a many to many type.
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?