Are Secondary keys supported

[jpl458]

Can you have a secondary key in access? Or can you just have 2 Key fields? Thinking of breaking up a table into different, smaller tables.

 

[CJ_London]

what do you mean by a secondary key? multiple primary key?

and smaller tables which way? rows or columns?

Either way is likely to be less efficient unless this is to normalise your data

 

[sonic8]

Can you have a secondary key in access? Or can you just have 2 Key fields?

Yes, of course. A secondary key is just a unique key (index), which is not the primary key.

Thinking of breaking up a table into different, smaller tables.

In what way is that related to secondary keys? - You might be on a path to unnecessary dependencies.

 

[The_Doc_Man]

There are two ways to look at your question.

A Primary Key uniquely identifies a single record. If the key is primary, it is unique. That is a hard-and-fast rule of pretty much EVERY serious database - like ORACLE, INGRES, SYBASE, SQL Server, etc. ... and Access. There is in fact a general principle that REQUIRES that you do not have another unique key for the same purpose.

HOWEVER, a "secondary key" can exist in a couple of different senses. The secondary key would be represented by an INDEX on a table. See above for why that index would not be a unique index. BUT you can use that index for sorting or searching or grouping.

An index can be single-field or multi-field. A multi-field index is also called a COMPOUND index. A primary key can be simple or compound. A non-primary key can be simple or compound. Access allows (I think) 10 indexes on a table counting the primary as 1.

 

[Pat Hartman]

There are many situations where you would want to use an autonumber as the primary key and a different field as a unique index to prevent duplicates. For example, when you interface with a different application, that application might have a unique PK but since you do not control it, it is generally better for you to use an autonumber as the PK and instead use the other system's unique identifier as a unique index to prevent duplicates. One example might be a driver's license. Since they are issued by states, if you have employees from multiple states, you don't want to mix the IDs for multiple states into one field because there could be duplicates or conflicts in format. But, you want to define them as unique to avoid errors so you might make a compound unique index with StateID + DriversLicense. Also, combos will not work correctly unless you have a single unique identifier so the path of least resistance is to use autonumbers as your primary keys but define unique indexes to support business rules.

 

[jpl458]

Yes, of course. A secondary key is just a unique key (index), which is not the primary key.


In what way is that related to secondary keys? - You might be on a path to unnecessary dependencies.

There are many situations where you would want to use an autonumber as the primary key and a different field as a unique index to prevent duplicates. For example, when you interface with a different application, that application might have a unique PK but since you do not control it, it is generally better for you to use an autonumber as the PK and instead use the other system's unique identifier as a unique index to prevent duplicates. One example might be a driver's license. Since they are issued by states, if you have employees from multiple states, you don't want to mix the IDs for multiple states into one field because there could be duplicates or conflicts in format. But, you want to define them as unique to avoid errors so you might make a compound unique index with StateID + DriversLicense. Also, combos will not work correctly unless you have a single unique identifier so the path of least resistance is to use autonumbers as your primary keys but define unique indexes to support business rules.

Is there a way to define a data field as an index? I have a table that is getting wide and I am considering breaking it up, Just thinking

 

[The_Doc_Man]

Mechanically speaking, get onto the table's Field Definitions and look above the field grid. There should be a button in the ribbon for indexes.

HOWEVER, be careful about breaking up a table just because "it is getting wide." If that "breaking up" includes normalizing the data, then go for it. But if your goal was to have a 1-1 relationship between the two table parts... DON'T DO IT. Having a two-table 1-1 relationship is almost a guarantee of a headache.

If you can normalize your data as a way to reduce the width of the table, that is the correct way to approach the problem. If you table looks more like a spreadsheet (and is more often treated that way), you may have a serious structural problem that will eventually eat your lunch for you.

If you are not familiar with normalization, then it is time and past time to start reading. In this forum, just search for NORMALIZATION because this is a database forum. However, doing a web search, you must look for DATABASE NORMALIZATION because the word normalization also appears in politics, mathematics, chemistry, biochemistry, psychiatry, and a few other diverse topics. If you go the web route, I advise you to start with articles from the .EDU domain because articles from the .COM domain often want to sell you something. However, after a couple of good articles, the .COM sites are fair game and actually often DO have decent articles to contribute.

 

[sonic8]

Is there a way to define a data field as an index? I have a table that is getting wide and I am considering breaking it up, Just thinking

Of course. In the properties list of every column of a table in design view there is a property "Indexed". Set this property to "Yes (No Duplicates)" and you'll get a unique index (key) on that column. For more complex (e.g. multi-colum) indexes turn to the "Indexes" button in the ribbon, as @The_Doc_Man suggested.

Even though I'm repeating myself: Splitting up wide tables into a 1:1-relation does not necessarily involve creating additional (non-primary) keys on a table. Your newly created (partial) table usually should contain the very same column used as a primary key as the original table. Using a different key column in the newly created 1:1-table usually has no technical benefits and will only increase complexity of and confusion about the data model.

 

[jpl458]

Mechanically speaking, get onto the table's Field Definitions and look above the field grid. There should be a button in the ribbon for indexes.

HOWEVER, be careful about breaking up a table just because "it is getting wide." If that "breaking up" includes normalizing the data, then go for it. But if your goal was to have a 1-1 relationship between the two table parts... DON'T DO IT. Having a two-table 1-1 relationship is almost a guarantee of a headache.

If you can normalize your data as a way to reduce the width of the table, that is the correct way to approach the problem. If you table looks more like a spreadsheet (and is more often treated that way), you may have a serious structural problem that will eventually eat your lunch for you.

If you are not familiar with normalization, then it is time and past time to start reading. In this forum, just search for NORMALIZATION because this is a database forum. However, doing a web search, you must look for DATABASE NORMALIZATION because the word normalization also appears in politics, mathematics, chemistry, biochemistry, psychiatry, and a few other diverse topics. If you go the web route, I advise you to start with articles from the .EDU domain because articles from the .COM domain often want to sell you something. However, after a couple of good articles, the .COM sites are fair game and actually often DO have decent articles to contribute.

Thanks for the information. Will read.

 

[Pat Hartman]

I've spent 50+ years designing and developing business applications using relational databases and probably used 1-1 relationships 3 times. They are EXTREMELY rare and NEVER created because your table is too wide. As the others have suggested, if your table is too wide, it is almost certainly in need of normalization. Look for sets of fields with prefixes or suffixes, especially numeric ones. Sometimes the repeating groups are harder to spot because the names are all different. How about, electric, gas, oil, water, snow shovelling, lawn mowing, cable, etc. Can you identify the pattern even though the names are all different?

But just to be complete, in a 1-1 relationship, one of the tables is the "master". It is the one with the autonumber PK and it is always inserted first. The "child" table uses a long integer as it's PK and that same field is also the FK to the "master".

On indexes:
There are two ways to create indexes. One method is used for single field indexes and the second is used for multi-field indexes.
Open the table in design view
1. Use the Index property for single field indexes.
2. Open the indexes dialog to create multi-field indexes. Give the first row a name and set the property to unique if necessary. Then for each subsequent row, leave the name blank. Access supports a MAXIMUM of 10 fields in an index. SQL Server allows

32 I think.

 

[jpl458]

I've spent 50+ years designing and developing business applications using relational databases and probably used 1-1 relationships 3 times. They are EXTREMELY rare and NEVER created because your table is too wide. As the others have suggested, if your table is too wide, it is almost certainly in need of normalization. Look for sets of fields with prefixes or suffixes, especially numeric ones. Sometimes the repeating groups are harder to spot because the names are all different. How about, electric, gas, oil, water, snow shovelling, lawn mowing, cable, etc. Can you identify the pattern even though the names are all different?

But just to be complete, in a 1-1 relationship, one of the tables is the "master". It is the one with the autonumber PK and it is always inserted first. The "child" table uses a long integer as it's PK and that same field is also the FK to the "master".

On indexes:
There are two ways to create indexes. One method is used for single field indexes and the second is used for multi-field indexes.
Open the table in design view
1. Use the Index property for single field indexes.
2. Open the indexes dialog to create multi-field indexes. Give the first row a name and set the property to unique if necessary. Then for each subsequent row, leave the name blank. Access supports a MAXIMUM of 10 fields in an index. SQL Server allowsView attachment 103809 32 I think.

Thanks for the lesson. Will keep it as a reference