How to combine these 3 things: "check if ID is legitimate from other table", then "Insert" or "Update" by using Just 1 query

Question

Ok, Here is my problem.

I have a rating system that has 2 tables

User Table
uID - Name
1   - Tom
2   - Mary

Comment table
cID - uID
1   - 1
2   - 3
3   - 3

Rating Table
uID - cID - rate
1   - 2   - -1
2   - 2   - +1
3   - 1   - +1

Note:

-In User Table: uID is primary key

-in CommentTable: cID is primary key & uID is foreign key.

-In Rating Table: (uID - cID) is composite primary key. That means One user can not rate twice on same comment. Rate column has 2 options only +1 or -1.

-Also, 1 User can rate many comments & 1 comment can be rated by many people. However, a person can not vote for his own comment. For ex, Tom (uID=1 created the comment cID=1 so there should not exist uID=1 & cID=1 in Rating table cos uID=1 can't rate his own cID=1.

So there should not have any record like this in rating table

Rating Table
uID - cID - rate
1   - 1   - +1   //illegal record

Requirement, when a user vote any comment the system should check whether the comment was created by that person. If it was then it should reject, otherwise it should insert uID - cID - rate into Rating Table.

However, if the insert got uplicate then it should update only. For ex, Tom voted +1 for a comment2 (it will insert uID=1 - cID=2 - rate=+1 into Rating table) & then later he voted -1 for that same comment2 (it will update uID=1 - cID=2 - rate=-1 in Rating table rather than reject the vote)

Here is what I think

given uID, cID & rate parameters

insert into Rating (uID, cID, rate) value (?,?,?) on Duplicate key Update rate=values(rate)

But how can we check the illegal record?

I prefer to have just 1 query that do these 3 things: "check if ID is legitimate for insert or update", if it is then "Insert", & if the record already there then "Update"?

I am using MYSQL 5.0

If I understand your question correctly, you could enforce the FK-PK relation by using InnoDB instead of MyISAM? — Mr47, May 30 '14 at 11:09
I am using InnnoDB, but u can't enforce the same person to vote for his own comment — Tum, May 30 '14 at 11:10
Since [MySQL does not enforce check constraints](http://stackoverflow.com/a/2115641/1048425) you would need to use a trigger to check the record is valid before the insert. — GarethD, May 30 '14 at 11:10
but I don't like trigger. I prefer 1 query that can do all things — Tum, May 30 '14 at 11:11
Just (outer) join the table to itself on x = y WHERE y IS NULL — Strawberry, May 30 '14 at 11:21

GarethD · Accepted Answer · 2014-05-30T12:04:52.797

1

You can create a procedure to do the insert, and check that the user is not voting on their own comment:

CREATE PROCEDURE InsertRating (uid INT, cid INT, rate INT)
BEGIN
    INSERT Rating (uID,cID, rate) 
    SELECT uID,cID,rate
    FROM Dual
    WHERE NOT EXISTS (SELECT 1 FROM Comment AS c WHERE c.CID = cid AND c.uid = uid)
    ON DUPLICATE KEY UPDATE rate=values(rate);
END

Example on SQL Fiddle

Be aware though this does not actually maintain the integrity, it just provides a method to stop illegal inserts, it does not stop someone coming along and running:

INSERT Rating VALUES (1, 1, 1);

This can be more strictly enforced by using a trigger.

CREATE TRIGGER RatingBeforeInsert BEFORE INSERT ON Rating
FOR EACH ROW
BEGIN
  SET @found := false;
  SELECT TRUE INTO @found FROM Comment AS c WHERE c.cid = NEW.cid AND c.uid = NEW.uid;

    IF @found THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Illegal INSERT';
    END IF;
END

Then even if someone tries to insert without the procedure the insert will fail. So unless they have the ability to drop the trigger, you will stop illegal inserts.

edited May 30 '14 at 12:04

answered May 30 '14 at 11:33

GarethD

68,045
10
83
123

"FROM Dual WHERE NOT EXISTS" what dual table? – Tum May 30 '14 at 11:56
[Dual table](http://en.wikipedia.org/wiki/DUAL_table) is an internal table with a single row in. MySQL states it is not required, however when I try and create the procedure without it I get a syntax error. – GarethD May 30 '14 at 12:02
when created trigger it said "Undeclared variable: cID" – Tum May 30 '14 at 12:04
I have tried adding aliases where appropriate, however this worked on [SQL Fiddle](http://sqlfiddle.com/#!2/4e661b/1), so I don't know why you are getting this error. – GarethD May 30 '14 at 12:07
i am using MYSQL5.0 not sure that is the problem. Besides, can i just use the "insert" query in StoreProcedure without creating store procedure? – Tum May 30 '14 at 12:11
Yes, the insert query will work fine while not in a stored procedure. – GarethD May 30 '14 at 12:19

How to combine these 3 things: "check if ID is legitimate from other table", then "Insert" or "Update" by using Just 1 query

1 Answers1