Ejb3: mapping many-to-many relationships compatible with a simple primary key

Question

often in books, I see that when a many-to-many relationship is translated into a database schema, JoinTable gets a composite key consisting of the primary keys of tables associated with many many-to-many relationships. I try to completely avoid compound keys. Therefore, I usually create a surrogate key even for JoinTable and let the database fill it with a trigger or any other function that the database has to increase primary keys. This seems like a much simpler approach.

The only problem I can think is that there is a chance of duplicating a pair of foreign keys in JoinTable. But this can be avoided by a simple query before the row is inserted into JoinTable.

Since books always use a composite key approach, I would like to know if there are any negative consequences if I use simple keys for surrogate columns for JoinTable?

Answer 1

In my opinion, using one primary key is a bad idea.

At first, as you said, a single primary key does not guarantee uniqueness in the database. Of course, you can verify this at runtime using a simple query, but it is expensive, and if you forget to execute your validation request only once, you can put your database in an inconsistent state.

, , . , : . , , , .

Answer 2

.

? ? , ? ?

JoinTable , . .

, . , .

, , JoinTable. , ​​ JoinTable.

, SELECT ( , SELECT ), UNIQUE, UNIQUE.

, SELECT , ? , , . .

, , - , JoinTable?

, - :

   A              A_B                 B
-------      ------------------    --------
ID (PK)      ID (PK),              ID (PK)
             A_ID (FK),
             B_ID (FK),
             UNIQUE(A_ID, B_ID)   

, ( JPA, - ). :

• - (m: n), , .
• A_B Entity ( , , . № 1).
  • (A_ID, B_ID) , ( )?
  • , .

, .