2

I have a table containing several columns and I want to make sure that two particular columns don't have the same value. Actually I don't want to allow a user to report an error several times. Before inserting the report I want to make sure that the same user haven't reported the same content, so there shouldn't be a row with a similar user ID and content ID. What I have in mind is sending a query to check if it returns a result, and if it doesn't then insert the report. But there must be a better way.

Thanks for your help.

André
  • 287
  • 2
  • 16

1 Answers1

7

You can create a composite index on the user ID and content ID, and have a UNIQUE constraint on that index.

Let's assume you have a report table:

alter table report add unique index(user_id, content_id);

Now when you try to insert a record that duplicates a user and content id pair, you'll get an error which you can handle appropriately in your application code.

Ezequiel Muns
  • 7,492
  • 33
  • 57