Access what is primary key

News and Special Offers occasional. Primary Key Reviewed by. Full Bio. View our Editorial Policy. Techopedia Explains Primary Key. What Does Primary Key Mean? It cannot contain null values. Every row must have a primary key value. Techopedia Explains Primary Key The primary key concept is critical to an efficient relational database.

Related Question What is the difference between a composite key, primary key and foreign key? Synonyms Primary Keyword. Share this Term. Tech moves fast!

Stay ahead of the curve with Techopedia! Join nearly , subscribers who receive actionable tech insights from Techopedia. Thank you for subscribing to our newsletter! 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.

Do I need a Primary Key and what happens if I don't define one? Here are some tips for creating primary keys. As with all rules, there are specific situations where one may deviate from them. Additionally, if your tables are small, adjusting the primary key may have no perceivable impact, so these tips may not matter. However, for larger tables and to support scalability over time, these tips can have a huge impact. The hope is to know these reasons and why you may intentionally deviate from these best practices.

The primary key should be the first field or fields in your table design. While most databases allow you to define primary keys on any field in your table, the common convention and what the next developer will expect, is the primary key field s coming first.

The values in a table's primary key should never change over time. For instance, a numeric customer ID can point to a customer, and regardless of whether they change their name, address, phone number, etc. If a primary key value has meaning, it can change which requires updating the tables that reference this value.

That may be automatic through cascading referential integrity, but it adds overhead, slows performance, and may create locking conflicts as the data is updated. Therefore, it's critical to avoid using things like people's names as primary keys.

Fields with meaning can also result in duplicates, which is a problem when the primary key is supposed to define each record uniquely. Fortunately, it's easy to define a unique primary key. It doesn't matter if you delete a record and the key values are no longer consecutive. The primary key should have no meaning other than the permanent tag to that record. A reasonable exception to this is geographic lists such as Countries, States, and Zip Codes, where the name is keyed rather than using a separate ID.

These data are relatively stable and small. Uniquely tagging a record can be done with a number long integer. There's no need to have more than one field defining its uniqueness.

Text fields require more bytes than numeric fields, so using a number saves considerable space. Making the primary key as small as possible reduces the memory required to open the primary key when links or searches are performed. Primary key size also impacts secondary indexes. A secondary index contains the sorted information on the field or fields in the index, plus a reference back to the primary key. Searching on the secondary index is fast because it uses a presorted list, but still points back to the record using the primary key to retrieve all of the record's other fields.

The smaller the primary key, the smaller the secondary index, and the faster the links and searches are. If a table has lots of secondary indexes and lots of records, the difference can be very significant if you are not using a single numeric field as your primary key. Sometimes people justify composite multi-field primary keys to avoid duplicates. This is the wrong approach. To prevent duplicates, create a secondary index for those fields and define it as unique. That lets the secondary index handle that job while the single field primary index retains its efficiencies.

Our Total Access Analyzer add-in performs detailed analysis of your MS Access database objects and detects tables with non-optimal primary keys, field type inconsistencies, plus hundreds of others tips to improve your database designs.

Microsoft Access within an Organization's Database Strategy. Microsoft Access vs. How many simultaneous Microsoft Access users? Blaming Microsoft Access instead of the Developer. Microsoft Access Version Feature Differences. Split Database Architecture for Multiuser. Table Normalization Tips. Take Time into Account. Temporary Data. Transpose Data. Avoid Unnecessary or Duplicate Indexes.

Replace Attachment Field Paperclip Icon. Avoid Decimal Data Types. Microsoft Access Query Tips and Techniques. Form Tips and Mistakes. Copy Command Button and Keep Picture.

Form Navigation Caption. Synchronize Two Subforms. Multiple OpenArgs Values. Late Bind Tab Subforms. Subform Reference to Control Rather than Field.