Add unique constraint on fields from different tables-CodePudding

I have two tables/entities:

One table Users with these 3 fields : id | login | external_id

There is a unique constraint on external_id but not on login

And another table User_Platforms that have these 3 fields : id | user_id | platform_name

There is a @OneToMany relation between Users and Platforms. ( One user can have multiple platforms). Is there a way to put a unique constraint on the fields login ( from Users table) and platform_name ( from User_Platforms table) to avoid having multiple users with the same login on the same platform ?

I was thinking of duplicating login field inside User_Platforms table to be able to do it easily. Is there a better way ?

CodePudding user response：

UNIQUE constraints cannot span multiple tables. In the model you are presenting "as is" it's not possible to create a unique constraint that will ensure data quality.

However, if you are willing to add redundancy to the data model you can enforce the rule. You'll need to add the column login as a redundant copy in the second table. This will change the way you insert data in the second table, but will ensure data quality.

For example:

create table users (
  id int primary key not null,
  login varchar(10) not null,
  external_id varchar(10) not null,
  constraint uq1 unique (id, login)
    -- extra UNIQUE constraint for redundancy purposes
);

create table user_platforms (
  id int primary key not null,
  user_id varchar(10) not null,
  platform_name varchar(10) not null,
  login varchar(10) not null, -- new redundant column
  constraint fk1 foreign key (id, login) references users (id, login),
    -- FK ensures that the redundancy doesn't become stale
  constraint uq2 unique (platform_name, login) -- finally, here's the prize!
);