CODEWITHSUNDEEP
mysqldatabasedatabase-designmany-to-manyrelational-database
Dalton Tan
Dalton Tan

Reputation: 485

Should junction tables have more than one primary keys from another identifying table?

Here's an example: Originally I have 3 tables. Table B references Table A. So now Table B has two primary keys. One used as the original primary key and the other one to enforce its relationship with Tabe A. Then I want Table B to have a many-to-many relationship with Table X. As I'm adding the relationship, MySQL Workbench added Table Y with both of Table B primary keys and one primary key in Table X. So Table Y now has three primary keys.

It seems like the second primary key from Table B in the junction table is unnecessary since I can identify Table B with the original primary key. So do I still need the extra primary key? Or perhaps I should not have an identifying relationship between Table A and B?

Table A and B have a relationship something like User has many Post. Post must belong to a User. But Post already has a primary key of its own, so does the foreign key to User need to be a primary key?

EDIT

Here's the scenario (diagram link below). The tables I'm focusing on are snippet, snippet_topic and tag. From what I know, since every snippet must belong to a snippet_topic, it has an identifying relationship. So I used the identifying relationship in MySQL Workbench and they added snippet_topic ID as a primary key. Afterwhich I added a m:n relationship for tag and snippet. MySQL Workbench added snippet_topic ID to the junction table (but I removed it). Is there anything wrong with my design? Or is there a more correct way to this?

Legend: Yellow icon - primary key Red icon - not null Workbench EER Diagram

Upvotes: 1

Views: 2073

Answers (4)

Simon Thompson
Simon Thompson

Reputation: 704

From your edited post - the only way to do this correctly is to have another table to hold the many to many relationship. Sniipit & snippit_topic have single primary keys and this new table has two columns with each of the primary keys

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

Originally I have 3 tables.

Ok.

Table B references Table A. So now Table B has two primary keys. One used as the original primary key and the other one to enforce its relationship with Tabe A.

No. Table B has one primary key and one foreign key. The foreign key might be part of the primary key, and it might not.

Then I want Table B to have a many-to-many relationship with Table X.

Ok.

As I'm adding the relationship, MySQL Workbench added Table Y with both of Table B primary keys and one primary key in Table X. So Table Y now has three primary keys.

A many-to-many relationship is typically implemented as a table that contains two foreign keys as its primary key. In your case, one foreign key is the primary key of Table B, and the other foreign key is the primary key of Table X. The primary key of Table B seems to contain two columns. The tables might look like this.

  • Table A: {a1, a2}
  • Table B: {b1, a1, b2, b3}, a1 references Table A
  • Table X: {x1, x2}

Table Y, which implements the m:n relationship, contains the keys from B and from X.

  • Table Y: {b1, a1, x1}, the two columns b1, a1 reference Table B; the column x1 references Table X

If you want better answers, edit your question and include the SQL DDL for your tables. Get the DDL by using SHOW CREATE TABLE.

Upvotes: 1

Simon Thompson
Simon Thompson

Reputation: 704

each table should only have one primary key which is only about this table. If you then want a second column in Table A containing the values of the table B primary key thats find. Just set up a second index to get performance if requires

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

You can not have more than one primary key. What you have there might be indexes. If the user_id column from table posts is included in your primary key, you can take it out and leave the primary key composed of just the id column.

I hope this helps

Upvotes: 0

Related Questions