0

A user has messages and a votes has messages and users. I want to make it so a user cannot vote on their own message. I am not sure how to create this SQL constraint though. What MySQL constraint can I use to make it so users cannot vote on their own messages?

DROP DATABASE IF EXISTS uncovery;
CREATE DATABASE uncovery;

SET foreign_key_checks=0;

USE uncovery;

CREATE TABLE marks (
  id int(5) AUTO_INCREMENT,
  PRIMARY KEY (id),
  x float(10, 6) NOT NULL,
  y float(10, 6) NOT NULL,
  z float(10, 6) NOT NULL,
  timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
  messageId int(5) NULL,
  commentId int(5) NULL,
  userToken VARCHAR(255),
  FOREIGN KEY (messageId) REFERENCES messages(id),
  FOREIGN KEY (commentId) REFERENCES comments(id),
  FOREIGN KEY (userToken) REFERENCES users(token)
);

CREATE TABLE messages (
  id int(5) AUTO_INCREMENT,
  userToken VARCHAR(255),
  messageString text NOT NULL,
  image VARCHAR(255),
  score int(5) DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id int(5) AUTO_INCREMENT,
  commentString text NOT NULL,
  messageId int(5),
  PRIMARY KEY (id),
  FOREIGN KEY (messageId) REFERENCES messages(id)
);

CREATE TABLE votes (
  id int(5) AUTO_INCREMENT,
  PRIMARY KEY(id),
  userToken VARCHAR(255) NOT NULL,
  messageId int(5) NULL,
  commentId int(5) NULL,
  FOREIGN KEY (userToken) REFERENCES users(token),
  FOREIGN KEY (messageId) REFERENCES messages(id),
  FOREIGN KEY (commentId) REFERENCES comments(id),
  UNIQUE KEY (userToken, messageId),
  UNIQUE KEY (userToken, commentId)
);

CREATE TABLE users (
  token VARCHAR(255),
  PRIMARY KEY(token),
  total_votes int(5) DEFAULT 0
);

-- If a message does not have a userToken then this will not work
DELIMITER //
CREATE TRIGGER vote_increment AFTER INSERT ON votes
FOR EACH ROW
  BEGIN
    UPDATE users SET users.total_votes = (users.total_votes + 1) WHERE users.token = 
      (SELECT userToken FROM messages WHERE id = NEW.messageId);
  END;//
DELIMITER ;
chopper draw lion4
  • 12,401
  • 13
  • 53
  • 100
  • 1
    I do not see how this is doable seeing as the two columns are in separate tables. Unless you somehow put the userToken inside the messageId – Joshua Byer Apr 15 '15 at 22:02
  • 1
    I would say that it's not the database's job to handle this kind of an issue, though it might be an extra measure in addition to the middleware checks. – Sami Kuhmonen Apr 15 '15 at 22:04
  • You'd have to use triggers : http://stackoverflow.com/questions/14247655/mysql-check-constraint – Rafael F. Apr 15 '15 at 22:19

0 Answers0