9

In database systems, should every table have a primary key?

For example I have a table table1(foreignkey1,foreignkey2,attribute) like this.table1 does not have a primary key.

Should I define a primary key for this table like table1id?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user2556081
  • 93
  • 1
  • 5
  • Think about type of queries you are going to use on this table and decide... – SMA Dec 19 '15 at 14:34
  • Theoretically, 'weak entity' is part of a relational database. But who can ensure no duplicate rows occur in the real world? https://en.wikipedia.org/wiki/Weak_entity – Dan Sin Dec 19 '15 at 15:42

4 Answers4

7

This is a subjective question, so I hope you don't mind me answering with some opinion :)

In the vast majority of tables I've made – I'm talking 95%+ – I've added a primary key, and been glad I did. This is either the most critical unique field in my table (think "social security number") or, more often than not, just an auto-incrementing number that allows me to quickly and easily refer to a field when querying.

This latter use is the most common, and it even has its own name: a "surrogate" or "synthetic" key. This is a value auto-generated by the database and not derived from your application data. If you want to add relations between your tables, this surrogate key is immediately helpful as a foreign key. As someone else answered, these keys are so common that MySQL likes to add one even if you don't, so I'd suggest that means the consensus is very heavily biased towards adding primary keys.

One other thing I like about primary keys is that they help convey your intent to others reading your table schemata and also to your DMBS: "this bit is how I intend to identify my rows uniquely, don't let me try to break that rule!"

To answer your question specifically: no, a primary key is not necessary. But realistically if you intend to store data in the table for any period of time beyond a few minutes, I would very strongly recommend you add one.

TwoStraws
  • 12,862
  • 3
  • 57
  • 71
4

No, it is not required for every table to have a primary key. Whether or not a table should have a primary key is based on requirements of your database. Even though this is allowed it is bad practice because it allows for one to add duplicate rows further preventing the unique identification of rows. Which contradicts the underline purposes of having a database.

Salim JB
  • 41
  • 4
2

I am a strong fan of synthetic primary keys. These are auto-incremented columns that uniquely identify each row.

These provide functionality such as:

  • Ability to see the order of insertion of rows. Which were inserted most recently?
  • Ability to create a foreign key relationship to the table. You might not need one now, but it might be useful in the future.
  • Ability to rename "data" columns without affecting other tables.

Presumably, for your table, you can define a primary key on (foreignkey1, foreighkey2). Composite primary keys are also sensible, but they are cumbersome for foreign key relationships and joins. And, when there are foreign key relationships, they may cause additional storage, because the composite key ends up being stored across multiple tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

It's a good practise to have a primary key/composite primary key for a table:

  1. it helps to join tables,

  2. clustered tables will need primary key.

Database design should have primary key for a table.

In MySQL 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.

You can create Composite Primary key like:

CREATE TABLE table1(
    FK1 INT,
    FK2 INT,
    ATTRIBUTE INT,
    PRIMARY KEY (FK1, FK2)
)

or create a constraint on table1:

ALTER TABLE table_name
ADD CONSTRAINT pk_table1 PRIMARY KEY (FK1,FK2)
Jarvis
  • 8,494
  • 3
  • 27
  • 58
Abhijeet Kale
  • 1,656
  • 1
  • 16
  • 34