Getting - there is no unique constraint matching

So I have a table called rooms which looks like this <image-1>, and I'm trying to have another table for room messages <image-2>, and one column of that links to the room, through the room_id but as you can see it doesn't get linked (the icon is not white), and I get this error whenever I try to link it as a reference <image-3>

garyaustin•11/27/22, 3:18 PM

If you made room id your primary key alone then you could link to it. But because you have it with other columns as the primary key, Postgres does not know if room_id is unique. One possibility is to make id in room unique, the correct thing is to point to combined primary key (all three columns). I have not done that in the UI so don't know if it is an option or not. You may need to do that by adding a foreign key constraint with SQL.

rinorzkOP•11/29/22, 8:20 AM

@garyaustin thanks, I ended up removing those keys as primary ones. I have another question though if you could help me with. So I have communities, which have rooms, which have messages, I also have a function which I can check when ever a user is member of community (which takes the community id, and user id), I wanted to integrate that into the RLS of the "room_messages" table, but those rows only have the room_id, should I also include the community_id or should I query first the rooms that match that id, and then get the community id that the room has and then call function? Is the first option faster? assuming for the second one it could take a bit longer every time to search for each room and then community and so on?

Getting - there is no unique constraint matching

Similar Threads

Similar Threads

Similar Threads