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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in October 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top