Why is it a bad idea to have a table without a primary key?

Question

I am very new to data modeling, and according to the Microsoft Entity Framework, tables without primary keys are not allowed, and this is clearly a bad idea. I'm trying to understand why this is a bad idea, and how to fix my model so that I do not have this hole.

I have 4 tables in my current model: User, City, HelloCity and RateCity. It is modeled as shown. The idea is that many users can visit many cities, and the user can rate the city only once, but they can greet the city many times. For this reason, I did not have PK in the HelloCity table.

Any understanding of how I can change this in line with best practices, and why is this contrary to best practices from the start?

Answer 1

This answer is mainly based on opinion / experience, so I will list a few reasons that come to mind. Please note that this is not exhaustive.

Here are a few reasons why you should use primary keys (PK):

1. They allow you to uniquely identify a given row in a table to ensure that there are no duplicates.
2. The DBMS applies this restriction for you, so you do not need to write additional code to check for duplicates before inserting, avoiding a full table scan, which implies higher performance.
3. PK (FK) , "" . PK/FK , "PK", "FK" , "PK" . , .
4. . TableA.id TableB.table_a_id? TableB.table_a_id = 5 , id = 5 TableA. , .
5. , b/c PK- , , - (, ).

, PK (.. ), ( ). , / , , ( ), (.. , " "), , , .

, , , .

M:M, , , PK , PK 2 PK .

, M:M A B, C, 1:M A B. "" :

+---+ 1  M +---+ M  1 +---+
| A |------| C |------| B |
+---+      +---+      +---+

C PK :

+-----+
|  C  |
+-----+
| id  |  <-- C.id = A.id + B.id (i.e. combined/concatenated, not addition!)
+-----+

:

+-------+
|   C   |
+-------+
| a_id  |  <--|
+-------+     +-- composite PK instead
| b_id  |  <--|   of concatenation (recommended)
+-------+

Answer 2

A . , . , , - (, ), . , ( HelloCity) , User, , .

, HelloCity User City. ? City User , . , , DisplayName - . User. , , DisplayName; , DisplayName, , .

, : City User ( , ), , , JOIN .

index (). , WHERE . , , , , .

, . , , , , . , .

HelloCity - Entity Framework , . , . , " ", , , . , , ? , , , , , .

, - , CityId, UserId , ( ).

Answer 3

Two main reasons for a primary key:

• To uniquely identify the entry for subsequent reference.
• To effectively and efficiently connect to other tables.