How can I add a constraint to this condition in MySQL?
My Table looks like this:
CREATE TABLE IF NOT EXISTS `entry_title` ( `entry_title_id` int(11) NOT NULL AUTO_INCREMENT, `entry_id` int(11) NOT NULL, `accepted` tinyint(1) DEFAULT NULL, `entry_title_lang` char(2) CHARACTER SET ascii NOT NULL, `entry_title_value` varchar(255) NOT NULL, `entry_title_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`entry_title_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
A row in the table represents a title for a content on a website.
The idea is that anyone can submit a new (hopefully improved) title.
Then the community accepts or discards the change.
If the accepted flag equals NULL this represents that the change is pending review by the community. 0 is interpreted as discarded and 1 as accepted.
The website displays the title with the most recent timestamp where the accepted flag equals 1.
When a change is pending review I no other change can be submitted until the pending one has either been accepted or declined.
Therefore i want a constraint in my database that makes sure that there is only row per entry_id where the value of accepted is NULL.
I thought about using a seperate field pending_review which is either 1 or NULL and put a UNIQUE constraint on it in combination with entry_id.
The problem with that is that I would somehow need to unset that field when the change gets accepted or declined and consistency on that level would call for another constraint that kind of leads to the same problem as the simpler solution above.
[updated] In the standard-driven ideal world:
CHECK(NOT EXISTS(SELECT 1 FROM entry_title WHERE accepted IS NULL GROUP BY entry_id HAVING COUNT(*) > 1))
Alas, we live in imperfect world. See this question
So use trigger with the same logic instead.
[update - trigger]
CREATE TRIGGER triggerName BEFORE INSERT ON entry_title FOR EACH ROW BEGIN IF EXISTS(SELECT 1 FROM entry_title WHERE accepted IS NULL AND entry_id = NEW.entry_id GROUP BY entry_id HAVING COUNT(*) > 1) THEN SIGNAL SQLSTATE '45000' END IF; END
and also do same thing for BEFORE UPDATE. disclaimer, I did not check this.