CODEWITHSUNDEEP
sql-serverdatabase-designmany-to-many
Tan Sang
Tan Sang

Reputation: 2069

What should I choose between unique column with index primary key and key has many values?

I have 2 table with many - many relationships. So I use the third table to mapping 2 table together. And I don't know what should I choose between having an auto-incremented, integer primary key then put unique indexes on the others or use a primary key have many values. What are benefits of each way?

Thank a lot

Upvotes: 4

Views: 140

Answers (2)

Gholamali Irani
Gholamali Irani

Reputation: 4350

In theory of Data Modeling, both solutions are correct.

But in practice: It's better to use second solution.
Taking new Auto-Increment ID and set it as Primary-Key and set two transmitted foreign keys as Unique (together).

Advantages:
Redundancy of Data: Assume that we have two tables named A and B and AB is new created middle table (because of many-to-many relationship).
Now if AB has a new relationship (one-to-many) with C. We should transmit Primary-Key of AB as foreign key to C. So it's better to transform ID instead of two attributes. As the same way if C has a new relationship (one-to-many) with D ... and so on.

Disadvantages:
Access Performance to IDs: Although there is redundancy in first solution, but there is a performance to access IDs without using any JOINs. (Assume that in table C, we want to access of A and B IDs.) But, accessing only to IDs is not much used.

Upvotes: 1

nvogel
nvogel

Reputation: 25526

In a Microsoft SQL Server database there is no significant difference between a PRIMARY KEY constraint and a UNIQUE constraint on non-nullable columns. The PRIMARY KEY is essentially just syntactic sugar. Convention and individual preference are the most common reasons for using the PRIMARY KEY constraint.

In any DBMS what really matters is what keys you have and how you use them, not which keys you designate as "primary".

Upvotes: 1

Related Questions