CODEWITHSUNDEEP
mysqlsqlconstraints
ditto
ditto

Reputation: 6287

MySQL: Creating a Constraint with WHERE Clause

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

Upvotes: 2

Views: 2280

Answers (1)

zerkms
zerkms

Reputation: 254956

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

Upvotes: 4

Related Questions