Hello Guy

I have three tables where table1 has 1 to many relationship to table 2.

i.e

table1 (table1_id, table1_name)
table2 (table2_id, table2_name, table1_id)

table3 has a m-to-m relation to table2, But I want to enforce in this relation that table1_id and table3_id is unique

i.e
table3 (table3_id, name)
table2_table3 (table3_id, table2_id, table1_id,
unique (table3_id,table1_id ) )

In the above there is a design flow because table1_id depends on table2_id and that means redundant data and may be inconsistent.

Also, I can do the design like this and use triggers to insure the uniqueness constraint.
table2_table3 (table3_id, table2_id)


My question, Is there is another design I am missing or option

You can add a redundant UNIQUE constraint to table2:

I say "redundant" because of course (table2_id) is the primary key and so (table2_id, anything) is trivially unique too. But now you can do this:

This will enforce the uniqueness and consistency of the data in table2_table3.

Thanks, This can solve it

Regards