Preventing MySQL insert if a row already contains INSERT data
Having a bit of a problem. Firstly, here's my table structure.
id user_id doc_id can_view can_edit can_delete
ID being the PK and user_id and doc_id being FK's of other tables.
Basically, what I need is to prevent an INSERT query from executing if the INSERT data for user_id and doc_id already exists in a row regardless of the rest of the columns.
if you have the data
user_id doc_id 1 1 1 2
And then you try do an INSERT with user_id = 1 and doc_id = 2, the query fails because there's already a row with that data in it.
Hope this makes sense.
Appreciate your help in advance.
Assuming there are no duplicates in doc_perms, you can create a unique index on (user_id, doc_id):
CREATE UNIQUE INDEX doc_perms_index ON doc_perms (user_id,doc_id)
If there are duplicates, you can use ALTER IGNORE ... ADD UNIQUE INDEX will remove them:
ALTER IGNORE TABLE doc_perms ADD UNIQUE INDEX doc_perms_index (user_id, doc_id)
(Among all rows that share the same (user_id, doc_id), all the rows except one will be dropped. The row that is kept is unspecified.)
After you create the unique index, INSERT INTO doc_perms ... will raise an error if (user_id, doc_id) is a duplicate.
If you wish to INSERT, but UPDATE other columns when (user_id, doc_id) is a duplicate, then (as @moopet has already mentioned) you can use INSERT ... ON DUPLICATE KEY UPDATE. For example:
INSERT INTO doc_perms (user_id, doc_id, foo) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE foo = 3'''