Is it bad to have a table without key?

Question

I have the following table, which represents the cards given to players during football matches:

CREATE TABLE cards (
    player_id INT,
    match_id INT,
    color VARCHAR(4),
    minute INT,

    FOREIGN KEY (player_id) REFERENCES players(id),
    FOREIGN KEY (match_id) REFERENCES matches(id)
);

As you can see, there is no primary key in it. It is possible for a player to receive two cards of the same color, at the same minute, in the same match. I think that is ok, since there is no need to know with more precision in which second a card is given.

Is this bad design? Does this have a name? Is this in any kind of normal form?

Possibly related: http://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key ; http://stackoverflow.com/questions/4621450/should-all-database-tables-have-a-primary-key — FrustratedWithFormsDesigner, Mar 06 '14 at 16:10
Most of the case, a database table without a primary key is a [weak-entity](http://en.wikipedia.org/wiki/Weak_entity) table, and normally we should always have a primary key for each database table. — albusshin, Mar 06 '14 at 16:11
@AlbusShin No, a weak entity still has a key. It's just that the foreign key is "within" the key. — Branko Dimitrijevic, Mar 08 '14 at 20:03

Mike Dinescu · Accepted Answer · 2014-03-06T16:16:18.013

Yes, in generally it's a bad idea to have a table without a primary key.

One reason for this is without a primary key there is no way to know which record you're referring to if you ever want to delete or update one of the records (if you have multiple with the same data as you explained).

Example:

 player_id    match_id      color      minute
 1            100           RED        34
 1            100           RED        34
 2            100           YELLOW     41
 3            200           RED        22

Now, imagine you want to update the second card that player 1 got, and change that to YELLOW (or remove it). How would you write that query?

So you can create a surrogate key for each record, if you expect to have multiple records for the same player, on the same match. Otherwise a natural primary key would be the combination of player_id and match_id.

CREATE TABLE cards (
    card_id INT PRIMARY KEY,
    player_id INT,
    match_id INT,
    color VARCHAR(4),
    minute INT,

    FOREIGN KEY (player_id) REFERENCES players(id),
    FOREIGN KEY (match_id) REFERENCES matches(id)
);

I don't understand how (player_id, match_id) could be a natural primary key. — aochagavia, Mar 06 '14 at 16:17
@aochagavia - if player_id/match_id combinations would be naturally distinct than that would be considered a natural key because it would uniquely describe a record. — Mike Dinescu, Mar 06 '14 at 21:00

score 3 · Answer 2 · answered Mar 06 '14 at 16:22

Just add a new attribute "NumberOfCards" then you can make {player,match,colour,minute} the key.

Is this bad design?

Yes

Is this in any kind of normal form?

No. Every column is nullable and duplicate rows are permitted so it isn't a proper relation and doesn't satisfy any normal form.

score 2 · Answer 3 · edited May 23 '17 at 12:28

Let me expand a little on what sqlvogel said....

In relational databases, "table" is a physical representation of the mathematical concept of "relation", which is a set of tuples. Since it's a set (and not multiset), it can contain each tuple at most once.

A table without key allows multiple identical rows/tuples, and therefore is not a relation (and your database can no longer be considered "relational").

In your particular case, you can just create a key out of your existing fields, and then add a "quantity" field that you can increment and decrement as needed. Alternatively, consider a more "finely grained" natural key, or even a "pseudo-surrogate" key as proposed by Miky Dinescu, that would allow you to truly distinguish between individual cards.

There are rare cases where a key-less table may be useful (when data is not very "important" but write performance is, such as when storing a debug trace), but in vast majority of cases, the lack of a key should be a big red flag.

Is it bad to have a table without key?

3 Answers3