• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

Is a primary key absolutely necessary?

I have a table of data that looks something like this:
11052985 SF P C 7.15 2 139000 135785.41
51054524 ZZ P A 5.55 1 122000 135675.43
21044984 SD Y G 2.35 1 123400 131175.45
11052985 ZZ P C 7.15 2 139000 135785.41
and so on.
There is no one field that is a unique value, e.g. notice that the first field in the 1st row and the 4th row
is the same.
I haven't assigned a primary key to this table. It seems it would have to be something artificial like this:
1 11052985 SF P C 7.15 2 139000 135785.41
2 51054524 ZZ P A 5.55 1 122000 135675.43
3 21044984 SD Y G 2.35 1 123400 131175.45
4 11052985 ZZ P C 7.15 2 139000 135785.41

Must I have a primary key?
What are the consequences of not having one?
If I must, is the way to do as I describe above?
0
allelopath
Asked:
allelopath
4 Solutions
 
Aleksandar BradarićSoftware DeveloperCommented:
Strictly speaking, it is not - you can have a table without a primary key. But, everything you want to do on that table will be _much_ easier if you do have a primary key. You'll have a narh time identifying the records... At least think of adding NON UNIQUE indexes, if you do not want to have a PRIMARY KEY.

On the other hand, the primary key can consist of multiple columns as any other index...
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> a narh time

a hard time
0
 
ldunscombeCommented:
I agree with leannonn.
While you may not see a need for one at the moment if in the future you need to link to another table or add search facilities etc it will be much easier and more efficient if you have a primary Key.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
IntellectUKCommented:
It's difficult to say for sure without knowing what the full requirements are, but PK's are not _absolutely_ necessary.  The problem comes when you need to link the records between different tables - how else do you refer to any particular row?

It's not uncommon to add a completely arbitrary key in the way you describe, although if the database has a high transaction-rate or is distributed then there are all sorts of considerations.  This would affect wether you use a sequential key like this, or perhaps GUIDs, or some other mechanism.

If two columns define a record then you can use compound indexes, but this makes links very complex.

Will this be a single table purely for storing some data, or will you be splitting/linking/exporting/importing/etc this data from/to somewhere?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
By the way, you can always include an additional field in the table description, like:
---
CREATE TABLE yourTable(
  id integer NOT NULL PRIMARY KEY AUTO_INCREMENT,
  your_field_1 ...
  your_field_2 ...
  ...
);

This gives you a primary key you don't have to worry about. It will generate values on it's own - no need to enter any data into the `id` field and you don't have to use it in your queries.
0
 
allelopathAuthor Commented:
The database is being built by a java program which is reading text file and writing to the db.

Let's call the first field , e.g. 11052985  in the 1st and 4th rows, the 'index number'
The primary use of the db will be to pull all rows of a given index number (or index numbers), e.g.
SELECT * FROM mytable WHERE indexnumber='11052985'

There should not be any duplicate rows. (I don't think this would be harmful, but it would be meaningless)

What is a non-unique index? From the sound of it, that may be the way to go. I don't see much about it on the www.
0
 
IntellectUKCommented:
A column can be indexed, but non-unique.  This is primarily to speed up filtering and linking.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> What is a non-unique index?

An index on any column(s) but does not require the uniqueness of data - you can have duplicates. Usually used for speeding up table queries.

More info: http://dev.mysql.com/doc/refman/5.0/en/create-index.html
0
 
NovaDenizenCommented:
You can also use combinations of columns as a primary key.  

Team        Number       Name
Steelers        7             Ben Roethlisberger
Jets               7             Ben Graham
...

In this table there are many Steelers players, and many players with the number 7, but each combination of (Team,Number) is unique.  

ALTER TABLE FootballPlayers add PRIMARY KEY (Team,Number)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now