CODEWITHSUNDEEP
sqlknex.js
Kanna'n Hassouna
Kanna'n Hassouna

Reputation: 102

add specific constraints to table

I would like to make a table called room_admins which is going to include the admins of chat rooms, so to do that I have made two foreign keys in the room_admins table first one will be for the room_id and the second one is going to be for the member_id.

I hope that's clear, but what I really need to add is a constraint to ensure that the member is actually a member in the room. like for example, if I have passed room_id 1 and member_id 1 the SQL has to ensure that the member of id 1 is really a member in the room with id 1

Upvotes: 1

Views: 38

Answers (1)

Schwern
Schwern

Reputation: 165200

Foreign key constraints can't do what you ask, and check constraints can generally only reference columns in the table. You could enforce it with a trigger, but that gets slow and complicated.

If you want to enforce that members are in a given room, add a table for room memberships and reference that. Rather than being a simple join table, this could contain other information about that member specific to that room, like what their nickname is.

room_memberships:
  id primary key
  nickname
  room_id not null references rooms(id)
  member_id not null references members(id)
  -- one membership per member and room
  unique(room_id, member_id)

At this point you can simply add an admin flag to room_memberships.

If a member can have multiple roles in a room, have a room_roles table which references room_memberships.

room_roles:
  room_membership_id not null references room_memberships(id)
  role not null
  unique(role, room_member_id)

How do we ensure the role is valid? This can be done with a check constraint, but then schema must be modified when you add a role. Again we can solve this with a new table. Add a roles table and reference it.

room_roles:
  room_membership_id not null references room_memberships(id)
  role_id not null references roles(id)
  unique(role_id, room_member_id)

roles:
  id primary key
  name not null
  unique(name)

Upvotes: 2

Related Questions