Should each and every table have a primary key

Question

I m creating a database table and I don t have a logical primary key assigned to it  So  I m thinking about leaving it without a primary key  but I m feeling a bit guilty about it  Should I    Should each and every table have a primary key

User · Answer

If you are using Hibernate its not possible to create an Entity without a primary key  This issues can create problem if you are working with an existing database which was created with plain sql ddl scripts  and no primary key was added

User · Answer

In short  no  However  you need to keep in mind that certain client access CRUD operations require it  For future proofing  I tend to always utilize primary keys

User · Answer

I know that in order to use certain features of the gridview in  NET  you need a primary key in order for the gridview to know which row needs updating deleting   General practice should be to have a primary key or primary key cluster   I personally prefer the former

User · Answer

It is a good practice to have a PK on every table  but it s not a MUST  Most probably you will need a unique index  and or a clustered index  which is PK or not  depending on your need   Check out the Primary Keys and Clustered Indexes sections on Books Online  for SQL Server    PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table  No two rows in a table can have the same primary key value  You cannot enter NULL for any column in a primary key  We recommend using a small  integer column as a primary key  Each table should have a primary key  A column or combination of columns that qualify as a primary key value is referred to as a candidate key    But then check this out also  http   www aisintl com case primary and foreign key html

User · Answer

I always have a primary key  even if in the beginning I don t have a purpose in mind yet for it  There have been a few times when I eventually need a PK in a table that doesn t have one and it s always more trouble to put it in later  I think there is more of an upside to always including one

User · Answer

I am in the role of maintaining application created by offshore development team  Now I am having all kinds of issues in the application because original database schema did not  contain PRIMARY KEYS  on some tables  So please dont let other people suffer because of your poor design  It is always good idea to have primary keys on tables

User · Answer

Late to the party but I wanted to add my two cents   Should each and every table have a primary key    If you are talking about  quot Relational Albegra quot   the answer is Yes  Modelling data this way requires the entities and tables to have a primary key  The problem with relational algebra  apart from the fact there are like 20 different  mismatching flavors of it   is that it only exists in paper  You can t build real world applications using relational algebra   Now  if you are talking about databases from real world apps  they partially mostly adhere to the relational algebra  by taking the best of it and discarding the rest  Also  database engines offer massive non-relational functionality nowadays  it s 2020 now   So in this case the answer is No  In any case  99 9  of my real world tables have a primary key  but there are justifiable exceptions  Case in point  event log tables  multiple indexes  but not a single key in sight     Bottom line  in transactional applications that follow the entity relationship model it makes a lot of sense to have primary keys for almost  if not  all of the tables  If you ever decide to skip the primary key of a table  make sure you have a good reason for it  and you are prepared to defend your decision

User · Answer

Short answer  yes   Long answer    You need your table to be joinable on something If you want your table to be clustered  you need some kind of a primary key  If your table design does not need a primary key  rethink your design  most probably  you are missing something  Why keep identical records    In MySQL  the InnoDB storage engine always creates a primary key if you didn t specify it explicitly  thus making an extra column you don t have access to   Note that a primary key can be composite   If you have a many-to-many link table  you create the primary key on all fields involved in the link  Thus you ensure that you don t have two or more records describing one link   Besides the logical consistency issues  most RDBMS engines will benefit from including these fields in a unique index   And since any primary key involves creating a unique index  you should declare it and get both logical consistency and performance   See this article in my blog for why you should always create a unique index on unique data    Making an index UNIQUE   P S  There are some very  very special cases where you don t need a primary key   Mostly they include log tables which don t have any indexes for performance reasons

User · Answer

I d like to find something official like this - 15 6 2 1 Clustered and Secondary Indexes - MySQL   If the table has no PRIMARY KEY or suitable UNIQUE index  InnoDB internally generates a hidden clustered index named GEN CLUST INDEX on a synthetic column containing row ID values  The rows are ordered by the ID that InnoDB assigns to the rows in such a table  The row ID is a 6-byte field that increases monotonically as new rows are inserted  Thus  the rows ordered by the row ID are physically in insertion order   So  why not create primary key or something like it by yourself  Besides   ORM cannot identify this hidden ID  meaning that you cannot use ID in your code

User · Answer

To make it future proof you really should   If you want to replicate it you ll need one   If you want to join it to another table your life  and that of the  poor fools who have to maintain it next year  will be so much easier

User · Answer

Always best to have a primary key  This way it meets first normal form and allows you to continue along the database normalization path   As stated by others  there are some reasons not to have a primary key  but most will not be harmed if there is a primary key

User · Answer

Disagree with the suggested answer  The short answer is  NO   The purpose of the primary key is to uniquely identify a row on the table in order to form a relationship with another table  Traditionally  an auto-incremented integer value is used for this purpose  but there are variations to this   There are cases though  for example logging time-series data  where the existence of a such key is simply not needed and just takes up memory  Making a row unique is simply    not required    A small example  Table A  LogData  Columns   DateAndTime  UserId  AttribA  AttribB  AttribC etc      No Primary Key needed    Table B  User  Columns  Id  FirstName  LastName etc     Primary Key  Id  needed in order to be used as a  foreign key  to LogData table

User · Answer

Except for a few very rare cases  possibly a many-to-many relationship table  or a table you temporarily use for bulk-loading huge amounts of data   I would go with the saying           If it doesn t have a primary key  it s not a table       Marc

User · Answer

Will you ever need to join this table to other tables  Do you need a way to uniquely identify a record  If the answer is yes  you need a primary key  Assume your data is something like a customer table that has the names of the people who are customers  There may be no natural key because you need the addresses  emails  phone numbers  etc  to determine if this Sally Smith is different from that Sally Smith and you will be storing that information in related tables as the person can have mulitple phones  addesses  emails  etc  Suppose Sally Smith marries John Jones and becomes Sally Jones  If you don t have an artifical key onthe table  when you update the name  you just changed 7 Sally Smiths to Sally Jones even though only one of them got married and changed her name  And of course in this case withouth an artificial key how do you know which Sally Smith lives in Chicago and which one lives in LA   You say you have no natural key  therefore you don t have any combinations of field to make unique either  this makes the artficial key critical   I have found anytime I don t have a natural key  an artifical key is an absolute must for maintaining data integrity  If you do have a natural key  you can use that as the key field instead  But personally  unless the natural key is one field  I still prefer an artifical key and unique index on the natural key  You will regret it later if you don t put one in

User · Answer

Just add it  you will be sorry later when you didn t  selecting  deleting  linking  etc

User · Answer

Pretty much any time I ve created a table without a primary key  thinking I wouldn t need one  I ve ended up going back and adding one   I now create even my join tables with an auto-generated identity field that I use as the primary key

[database] Should each and every table have a primary key?

Examples related to database

Examples related to database-design