allelopath
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
A column can be indexed, but non-unique. This is primarily to speed up filtering and linking.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
a hard time