primary key on very small table

Question

Hi, I am having a very small tables with at most 5 records that holds some labels. I am using Postgres.

The structure is as follows:

id - smallint label - varchar(100)

The table will be used mainly to reference the rows from other tables. The question is if it's really necessary to have a primary key on id or to have just an index on the id or have them both?

I did read about indexes and primary keys and I understand that this depends quite a lot on what's the table going to be used for:

http://stackoverflow.com/questions/5600/tables-with-no-primary-key

Edit: I was going to ask about having a primary key or an index or have them both. I edited the question.

Answer 1

It is always good practice to have a primary key column. The typical scenario it is needed is when you want to update or delete a row, having a PK makes it much easier and safer.

Answer 2

Yes, you will need a primary key on the id field, since you do not want two labels that share the same id.

You also want an index, to speed up the search/lookup process in this table (although for small tables there is less performance gain). The sequence will just help you fill in the next ID; it does not prevent you from changing a previous value into one that already exists.

Answer 3

Yes, a primary key is not only good practice -- it's crucial. A table that lacks a unique key fails to be in First Normal Form.

You must declare a PRIMARY KEY or UNIQUE constraint if you want other tables to reference this one with a foreign key.

In most RDBMS brands, both PRIMARY KEY and UNIQUE constraints implicitly create an index on the column(s). If it doesn't do this implicitly, you may be required to define the index yourself before you can declare the constraint.

Answer 4

There are very little reasons for creating an index instead of a primary key. AS Bill Karwin said, you won't save resources at all. And, as you may have already guessed, there is no need at all to create a new index if you have the primary key.

In some cases it may be hard to find a key candidate. But it doesn't seems to be the case and it clearly goes against some good practices.

By the way. As your table is so small most queries will rather use a full table scan even if there is an index. Don't worry you see a full table scan.

Answer 5

From the developer's point of view, PRIMARY KEY is just a combination of NOT NULL and UNIQUE INDEX on one of the rows.

A UNIQUE INDEX is good if:

1. You need to enforce uniqueness. Using index is the most efficient way to do that.
2. You need to perform a SELECT, UPDATE or DELETE with a WHERE condition that is selective on the indexed field, that is number of rows affected by the query is much less that total number of the rows (say, 10 rows of 2,000,000).

A UNIQUE INDEX is bad if:

1. You don't need uniqueness on this field, of course :) But you'll better have one unique index in the table, for each record to be identifiable.
2. You need fast INSERTS
3. You need fast UPDATE's and DELETE's of the indexed value with a WHERE condition that is not selective on the indexed field, that is number of rows affected by the query is comparable to the total number of the rows (say, 1,500,000 rows of 2,000,000).

Given that you are going to have a little table, I'd advice you to create a PRIMARY KEY on it.