Q
Problem solve Get help with specific problems with your technologies, process and projects.

Purpose of a primary key

What is the actual need of a primary key? What will happen if I don't define a primary key?

That's a great question. 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. Whew! As I said, that's another discussion. In the meantime, go ahead and declare primary keys for your tables.

For More Information


This was last published in October 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close