SQL Implementation - Social Media Like Functionality

Question

I am building a full stack app, that is kind of like Pinterest or Instagram.

I've gotten stuck, which is a horrible feeling, and I'd love any help I could get from any kind soul out there! :)

So I'm trying to implement the "like" functionality. As y'all know, you can like an entity once, and only once. What I wonder is how to constrain the database in such a way that a user can't like more than once. The way I thought I'd do this is to have a separate Like table like this:

Now of course, if I UNIQUE the user_id, then users can only like one thing on the website, which obviously would be a disaster, hah. So my question is, how do I kind of make sure there are no duplicate rows with exactly the same information in them? Or make some kind of CHECK condition when creating the table so that there can't be more than one row where the user_id and the artpoem_id match?

Or am I thinking about this the wrong way? Should I have like a separate Like table for each and every ArtPoem-entry, and then UNIQUE the user_id?

I would be SO grateful for any help, as this has been bugging me for days. Thank you kindly!

It is possible to create unique constraints (and also other kind of keys) that consist of more than one column, see e.g. [How do I specify unique constraint for multiple columns in MySQL?](https://stackoverflow.com/q/635937). — Solarflare, Jul 30 '20 at 18:11
Hello, @Solarflare! Thank you so much for the quick answer, I will check this out! I use TypeOrm, hope I will be able to figure out how to use this method using that as well. :) — Dawid Dahl, Jul 30 '20 at 19:30

score 0 · Accepted Answer · answered Jul 30 '20 at 19:59

Problem solved! If anyone else sees this in the future, here's how it happened:

With the help of @Solarflare here who pointed me in the right direction, I did this in my ORM (TypeOrm):

Which yielded this raw SQL:

And now my database gives me this error below when I try to add an additional like-table entry with matching artpoemIds and userIds, which is just what I was looking for! Yippie and onwards!

SQL Implementation - Social Media Like Functionality

1 Answers1