-1

I have a scenario where i would need to add a unique constraint using columns from 2 different tables. I have 2 tables. The first table is like,

TABLE MODEL(
    ID PRIMARY_KEY,
    KEY
)

and the second table as,

TABLE OWNERS(
    ID PRIMARY_KEY,
    MODEL_ID FOREIGN_KEY REFERENCES MODEL.ID,
    USER
)

My requirement is the combination of MODEL.KEY + OWNERS.USER should be unique. How can we achieve this?

Note: I'm using Postgres as database. There is no possibility of adding OWNERS.USER to MODEL table.

Vamshi
  • 11
  • 4
  • What does "the combination of MODEL.KEY + OWNERS.USER should be unique" mean? In what table / collection of rows? Model cross join owners? Projection of model cross join projection of owners? (Same.) Please clarify via edits, not comments. [ask] [mre] PS Using SQL declarative constraints, you can have that as a base table & declare appropriate UNIQUE & FK constraints. Of course, update must be appropriate. PS Generic constraint enforcement in SQL requires triggers. PS What has your research shown? PS You give some limitation on schema change, but what is the definitive allowable shema change? – philipxy Oct 29 '21 at 04:18

1 Answers1

0

You cannot get that with your data model. You'd have to have an additional table owner_key or so that holds the key (as a primary key) and is referenced by both other tables.

Avoid a name like user for columns or tables, because that is a reserved SQL keyword.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi @Laurenz, i need the uniqueness for MODEL.KEY + OWNERS.USER. Say i have 2 entries with a key "Sales" in model table. With the approach you said there can be duplicate entries for a single user as ids are unique for the same key entries. – Vamshi Oct 29 '21 at 03:42
  • I see. I have adapted my answer. – Laurenz Albe Oct 29 '21 at 06:45