Should every table have Primary key?

Question

I believe my question is both conceptual and technical.

My whole problem started when I made an SQL Server database and in it there were some tables without primary key, such tables would serve within the application to record foreign key records, from the products table to the orders table and from the products table to catalog table, just so I could make the joins within the selects.

My application should use ASP.NET MVC because of the requirements of the course in using certain technologies, so in my group we decided to use the Entity Framework.

So far so good inside SQL Manager all working, but when the .edmx generated my models and context the tables that did not contain a primary key did not appear in the application.

Maybe it was my malpractice and so I ask you it would be only in the case of using the Entity that I should use primary key in all tables or use is a standard for any database application?

Answer 1

Unfortunately, there’s a little bit of a bad decision there for choosing to use a technology that is virtually abandoned (perhaps choice of course). Nothing critical because it is something for a course and the project will be abandoned (I think), but at the same time it is learning from something that has no future. It would be better to have used Entity Framework Core.

Yes, the FE requires that you have a primary key precisely because almost every table should have and it needs to have "where to hold on to". And in fact every table in a database has a PK, in some cases it’s invisible to you because your model might not require it, and then you think you don’t have one. But also almost always not having an explicit primary key indicates a wrong modeling. Why don’t you need one? Do you know the consequences of this? If you don’t know how to answer this then you should at least use the default which is to have a PK. It is not ideal because every decision must be justified, but the chance of making mistakes decreases since not having PK is a very exceptional case.

I say more, almost always the proper key is a substitute, which is used by SGDB if none is provided by you. It is very rare for a natural to be adequate, because even if it seems, one day it may not be more and then you will have work tidying up everything. The same reason you use a surrogate is what it takes to have an explicit PK.

There are no absolute answers to these things. I know that almost everyone wants to believe that they have, that it is enough to memorize a rule and always do what will be right, but the only right thing is to understand the fundamentals, know the reasons for each thing, and then make decisions according to that knowledge. Nowadays a lot of the problems of software (especially those that do not seem to be problems because they work, although it is not right) is because the decisions were taken on the basis of an automatic rule and not on the basis of grounds and considering the specific case that is being solved.

The doubt is only conceptual because there is no concrete case.

Answer 2

In the case of tables that only involve relationships between other tables, as mentioned by you, I usually create an "artificial" PK (an Identity or a guid).

This facilitates me direct access to the record, because I will need to inform only an "id" for any query, change or delete.

Answer 3

Nelson, the two answers are very complete. I will add a remark here regarding Nosql database.

Nosql Bank work in a completely different way from relational banks and, depending on the context, you don’t need to primary key only an identification of the record.

Knowledge is valid. Hug.