databasedev.co.uk - database solutions and downloads for microsoft access

Do I need a Primary Key and what happens if I don't define one?

The main purpose of a primary key is to implement a relationship between two tables in a relational database; it's not called a relational database for nothing!

More specifically, the primary key is the "target" which a foreign key can reference. You cannot declare a foreign key in table B to relate to table A unless the primary key in table A has been defined.

So right away, here's the first conclusion: if your database has a table which no other table relates to, then this table doesn't need a primary key. Nevertheless, the table can still have one, and it is common practice to declare one anyway.

One reason for declaring a primary key, even if no other table references it, comes from what we could call a side benefit - the primary key gets an index.

To the best of my knowledge, all databases utilize an index in order to implement the uniqueness constraint that a primary key requires. The reason is simple: when you tell the database to insert a new row, it has to check the new row's primary key value against all the existing primary key values to make sure the new value is not there yet. There's no faster way do accomplish this than by looking up the value in an index. You could say this is defensive behaviour on the part of the database, because it knows that looking up a value is always faster using an index than scanning a table.

So by declaring a primary key, you get an index, and chances are, you'll need it for retrieval anyway. Most applications have at least one query which is supposed to return a single row from the table. This is often based on the primary key, because the primary key is, by definition, capable of distinguishing each row from all the others. So when searching for a particular row using the value of the primary key, the database can uses the same index to speed up the retrieval that it set up for itself to ensure that the primary key will always be unique.

Yes, you can declare indexes yourself. In fact, you should declare an index on every column that needs one, but which ones need one is a different discussion for another day. Furthermore, you should not declare a primary key and also an index of your own on the primary key. That would be redundant, unless it's a composite primary key, in which case you might need additional indexes, at least on columns of the primary key not in the same order, or on subsets.