MySQL: Creating a Constraint with WHERE Clause

Question

I have a MySQL database:

CREATE TABLE `users votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `forumtopicid` int(11) DEFAULT NULL,
  `replyid` int(11) DEFAULT NULL
)

I'm trying to create two unique constraints:

CREATE UNIQUE INDEX index_first
ON `users votes`(userid, forumtopicid, replyid)
WHERE forumtopicid IS NOT NULL;

CREATE UNIQUE INDEX index_second 
ON `users votes`(userid, replyid) 
WHERE forumtopicid IS NULL;

This is throwing an error that I'm unsure how to fix. Is it not possible to add a WHERE clause for MySQL constraints?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE forumtopicid IS NOT NULL' at line 3

Since you have `NOT NULL` in the table schema, why do you need this constraint in the index? The field can never be null. — Barmar, Jan 07 '14 at 02:28

zerkms · Answer 1 · 2014-01-07T02:54:25.727

4

That's correct - you cannot use WHERE in CREATE INDEX statements (in mysql).

edited Jan 07 '14 at 02:54

answered Jan 07 '14 at 02:27

zerkms

249,484
69
436
539

Seems I misunderstood, http://stackoverflow.com/a/8289253/618584 works for postgresql but there is no MySQl alternative. :( – ditto Jan 07 '14 at 02:36

MySQL: Creating a Constraint with WHERE Clause

1 Answers1