Primary Key - Are they really needed

[BeeKeeper123]

I am a novice user of Microsoft Access. I typically do not use Primary Keys. Everything I read indicates that the primary key is used mainly to speed up the search of a database. I am currently just using two small tables which contain a common record ID. I am doing querries on these tables and I have not discovered any errors. Based on what I have seen I still obtain the correct information from my querries. Is it critical that I use a primary key or is a nice to have with large databases?
Thanks,
Beekeeper123

 

[jebry]

Hi!

Establishing a primary key is never necessary even in a large database. There are advantages to having a primary key mainly that Access will make sure that the field will contain only unique values. This will stop a user from accidentally entering a duplicate record. Access will also make sure that the primary key contains a value again this is necessary to keep each record uniquely identified.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[jrbarnett]

Jebry is correct in that a primary key is not required for small installations, but:

* For creating referential integrity between tables, it is essential. This helps maintain data between two tables, for example not having order details without a matching order.
* It helps speed up searching/sorting of data on those fields by setting up unique not null indexes on those field(s).
* It guarantees uniqueness of key values amongst the primary key field(s) within a table.

As the database size increases, you will find them going from nice to have to essential.

John

 

[pbrodsky]

You can always index fields for efficiency without creating a primary key. Without a primary key, however, the database engine cannot guarantee that each record has a unique identifier. A unique identifier is essential if you want to ensure that you are working with the correct record. Otherwise, you can have multiple identical or similar records and it can be difficult (or impossible) that you have located the correct one.

 

[KornGeek]

BeeKeeper123,

Just to see what happens, choose a record ID and duplicate the records in each table with that ID. Then, run your query. You'll probably notice about 4 copies of the record now.

Because you said that each record has a unique record ID anyway, you might as well create a primary key. I always use them, even if I only have a single table.

 

[alvechurchdata]

I always use them, even if I only have a single table

Same here. They cost nothing and it's very much easier to have one in place from Day 1 than it is to add one later.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lespaul]

Agree absolutely! I never create a table without one.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[pbrodsky]

And DON'T automatically think of a primary key as being an autonumber field. If you already have a unique identifier you should definitely use that instead.

 

[psemianonymous]

This is too crazy. Primary keys are fundamental to data integrity. If you don't believe me, ask the crazies at

http://www.dbdebunk.com/

- they're ALL about the theoretical advantages of these sorts of things.

But it all depends on what you're using Access for. Let me just summarize: Primary keys (and things like referential integrity) eliminate a whole class of subtle, hard-to-find errors.

 

[alvechurchdata]

If you already have a unique identifier you should definitely use that instead.

That's safe enough if you're dealing with things that never change - something like an invoice number for example. You can end up doing a lot of work if clients change unique identifiers.

Just to give one example. We wrote a database for a management college and used their course codes as the identifier for tables of courses, bookings, payments, mailshots and all sorts of other things. These codes had been in use for years - people didn't even talk about the "Presentation Skills" course, they talked about "PS".

After five or six years some marketing muppet changed all the course names and their codes. We applied global changes to the live data over a weekend then spent two weeks picking bugs out of the code.

That was some years ago but I always use a surrogate key now. It's always an integer, it's always called something_id and the client doesn't even know it's there. They can't see it and they can't alter it and they can't break it.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[pbrodsky]

After five or six years some marketing muppet changed all the course names and their codes. We applied global changes to the live data over a weekend then spent two weeks picking bugs out of the code.

That was some years ago but I always use a surrogate key now. It's always an integer, it's always called something_id and the client doesn't even know it's there. They can't see it and they can't alter it and they can't break it. [\quote]

If you started with perfectly good codes as primary keys, the fact that the users changed their [\b] codes doesn't hurt your database design any more than starting with an extra meaningless code would. You eventually need to refer to the actual values in either case, and in either case you need be able to cope with the user changing the system. If it's not practical to actually change the keys, you could just start treating the original keys as arbitrary values and creating another value to show the user.

I still don't really think adding an arbitrary value as a primary key unnecessarily is a good idea.

Obviously, as with everything else, there would be circumstances in which it would be a good idea.

 

[SeeThru]

Yup, always have a primary key, and try not to let users at it........

I often have the real primary key and a pseudo one (like invoice no). Al relationships use the real one, but only display the pseudo one.

Makes life easier when people mess with stuff.

 

[edsuk]

Totally agree with SeeThru.

I generally have a hidden primary key and display to the user a number which they believe to be the primary key, for example "Invoice Number","Enquiry Number" and "Job Number". In all essence these are unique but are not used when joining tables.

Therefore in the event that a user needs to modify one of these values they do not have to worry about the effects and are amazed when the amended value appears thoughout the database without problems (normally )

 

[KornGeek]

I personally don't like using an arbitrary made-up primary key if there is already a valid key available. For instance, if the InvoiceNumber field will always be unique, then I would use than rather than making up a new one.

To handle the event where a user changes this value, you can use referential integrity and cascading updates to handle that.

It's just a matter of personal preference.

 

[fneily]

Read E.F. Codd's A Relational Model of Data for Large Shared Data Banks Communications of the ACM, Vol. 13, No. 6, June 1970, pp. 377-387. Copyright © 1970, Association for Computing Machinery, Inc.
This established the first protocols for relational databases of which Access is one. He then went on to refine his principals. Anyone who says that Primary keys are not needed should read Relational Database theory books. Does your little database work for you? Yes. Is it relationally correct? Probably not. A little red wagon will get you to work. I'm sure you would prefer a car.

 

[PCLewis]

If you don't need one in your database, then by logical extension, it will not hurt you to have a primary key with an autonumber field type (I did not some posters might be assuming that a primary key must always be an autonumber field?).

By having it in your database you have provided a small insurance policy for the future. If you do add one and you use Access I would recommend naming the primary key something other than ID. Access by default likes to join ID fields in tables when you create a query with the query builder. For a table called Students, perhaps name your key StudentID, for a table called Customers, go for CustomerID. It's really easy to do, a good habit to have and will definetely be worth it in the long run.

Cheers

 

[lespaul]

I have to point out a differing view on the naming of primary keys. I usually avoid renaming the ID the same as the table, I only "name" the ID when it's used as a foreign key. For example:

Customers (tableName)
ID (PK)

Orders (tableName)
ID (PK)
CustomerID (FK)

that way when you are reading the fields in the query it looks like:

SELECT Customers.ID, Orders.ID, Orders.CustomerID FROM ....

instead of:

SELECT Customers.CustomerID, Orders.OrderID, Orders.CustomerID FROM ...

I just find it easier to read.