Creating a unique composite key, where one of its components is a foreign key-CodePudding

I'm using Postgres, and I'm trying to model a scoreboard app (say, where a single person is keeping track of scores for a board game).

As an example, Alice is a user on the app that wants to tally scores for herself and her friends Bob, Caden and David. None of her friends are users, but they don't need to make an account in order for Alice to add them to a scoreboard.

Later on, Caden is impressed with the app and decides to make an account. Alice then is able to link the Caden she added to her scoreboard to Caden's actual account on the app.

So I define a user as someone with an account. And a player as a "temporary" account that can later be linked to a user. A user can moderate players by adding them to a scoreboard. Here's my initial schema:

users

user_id	username	email
u1	alice	[email protected]
u2	caden	[email protected]

players

player_id	name
p1	Alice
p2	Bob
p3	Caden
p4	David

players_to_moderated_by

player_id	moderated_by (foreign key to users)
p1	u1
p2	u1
p3	u1
p4	u1

players_to_linked_to

player_id	linked_to (foreign key to users)
p1	u1
p3	u2

Now, I want to make two unique constraints

moderated_by and players.name so that a user cannot confusingly add two players with the same exact name on a scoreboard
moderated_by and linked_to so that a user cannot add two players that happen to be linked to the same user

However, in both cases the two components are keys/columns in separate tables. How do I define these constaints in SQL?

CodePudding user response：

It seems you have too many tables. Gather all data about a player in a single table. Example:

create table players(
    id int generated always as identity primary key,
    player_name text,
    moderated_by int references users,
    linked_to int references users
);

As linked_to may be null you need a partial unique index (that cannot be explicitly defined as a table constraint):

create unique index on players(player_name, moderated_by);
create unique index on players(moderated_by, linked_to) where linked_to is not null;