Preventing MySQL insert if a row already contains INSERT data

Having a bit of a problem. Firstly, here's my table structure.

Table doc_perms

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.

For example

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.

Answers


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'''

Need Your Help

Static linking DCMTK library

c++ linux linker cmake dcmtk

I use DCMTK in my application and for compilation use cmake file. cmake finds all libraries (at least headers, because in compiles source files to .o files) the only problem is that during linking it

Filter SystemApp from getInstalled app

java android

I am developing one app in which i am getting Installed app from system.and display in listview successfully. i got each thing successfully. but now here it getting me system app also that i dont w...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.