How can I add a constraint to this condition in MySQL?

Question

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.

Do you need to keep a history of past titles? Or just having a "current" title and optionally one more "pending" title is enough? — Branko Dimitrijevic, Jun 13 '12 at 10:24
i want to keep a history. because i want the conditions vor the voting to become harder based on a logarithmic function over the number of already submitted titles for that entry... — The Surrican, Jun 13 '12 at 10:29

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

[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]

Something like

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 13 '12 at 10:07

Evan

492
1
3
15

1

wups, just read MySQL doc: "The CHECK clause is parsed but ignored by all storage engines." tough luck. use something better, like PostgreSQL ^^ edit: looks like PostgreSQL doesn't support subqueries in check also. – Evan Jun 13 '12 at 10:21
yup i was hitting exactly that wall but was not sure about the cause. unfortunately i am not really familar with mysql triggers (input/output arguments, return values, when to call, etc...) – The Surrican Jun 13 '12 at 10:31
just updated my answer. that signal sqlstate returned an error that will make corresponding operation fails. I can't test this right now so please check it yourself. – Evan Jun 13 '12 at 10:50

How can I add a constraint to this condition in MySQL?

1 Answers1