Primary Key confusion (1 Viewer)

[kevnaff]

Hi All.

I have taken over a database and I have come across an issue with one table and its primary key that I don't understand.

We have a table called [Import Equipment] that stores information about all of the equipment that we own. Each piece of equipment is given a unique [Code No], and we put a label on each piece of equipment with this number as well as a barcode relating to this number on the label.

However the table does not use the [Code No] as its primary key. Instead it uses a number called [EquipID] as its primary key, which is an autonumber.

Is this common or can someone understand why this may be?

As the [Code No] is the number that we use to identify something, and never the [EquipID] number, it has become an issue in a few queries that I want to create. I want to create a new table with [Code No] in it, and then create a query to pull information about this [Code No] from the [Import Equipment] table. But as it's not the primary key, it doesn't seem to pull the information from the table properly.

Thanks

 

[Ranman256]

The autonum was not needed. You can use CODENO as the key. (and should have been)
Some folks don't check this and just assign an autonum.

 

[Gasman]

As the [Code No] is the number that we use to identify something, and never the [EquipID] number, it has become an issue in a few queries that I want to create. I want to create a new table with [Code No] in it, and then create a query to pull information about this [Code No] from the [Import Equipment] table. But as it's not the primary key, it doesn't seem to pull the information from the table properly.

Thanks

You would need to explain further, as they are just fields after all? Even if EquipID is the PK and linked to the other tables, no reason as to why you cannot get data on CodeNo?

 

[moke123]

Code No is obviously text datatype as it has leading zeros. Are you using text delimiters?

[Code No] = "00001"

In related tables is there a foreign key related to EquipID?

 

[The_Doc_Man]

If you want more in-depth discussion on this subject, look up "Natural Key" and "Synthetic Key" for a few opinion articles here. Including one rather extensive and forcefully argued discussion on the merits of each kind of key. It is not a recent thread.

 

[sonic8]

Is this common or can someone understand why this may be?

I'd say it is fairly common to use synthetic auto numbers rather than a natural key.
One reason that applies here is that numbers can be processed more efficiently than text and use less storage space. ("Code No" must be text for the leading zeros.)

But as it's not the primary key, it doesn't seem to pull the information from the table properly.

"Code No" not being the primary key should not make any difference for any query. However, if "Code No" is required and unique (as a PK must be), the column in the database should also have these attributes to let the database engine know about these facts.

 

[kevnaff]

The autonum was not needed. You can use CODENO as the key. (and should have been)
Some folks don't check this and just assign an autonum.

Hi Ranman,

Despite CODENO only ever in theory being unique, because no 2 pieces of equipment should have the same CODENO, for some reason Yes-Duplicates OK is selected for this value. I think this was an error that has been in place since the beginning of the database.

 

[kevnaff]

You would need to explain further, as they are just fields after all? Even if EquipID is the PK and linked to the other tables, no reason as to why you cannot get data on CodeNo?

Hi Gasman,

As someone pointed out above, I have mistakenly assumed CodeNo is a number due to the name, and instead it is short text. I created a new table with CodeNo in it as a number, and created a relationship between the 2 tables. When creating a query, I was getting a 'Data mismatch in expression' message. This is because one table was storing it as a number and one as short text.

 

[kevnaff]

Code No is obviously text datatype as it has leading zeros. Are you using text delimiters?

[Code No] = "00001"

In related tables is there a foreign key related to EquipID?

Hi Moke,

Thanks for pointing this out, I had assumed [Code No] was a number, but as you've showed it is in fact stored as short text. This has solved my issue to do with the query I've been wanting to create.

There was one table with a foreign key related to EquipID, but I don't believe it's ever used. I could delete this table and any relationships to EquipID and change the primary key to [Code No], but seems as though I've got my query working, I'll leave it as it is for now.

Thanks

 

[kevnaff]

If you want more in-depth discussion on this subject, look up "Natural Key" and "Synthetic Key" for a few opinion articles here. Including one rather extensive and forcefully argued discussion on the merits of each kind of key. It is not a recent thread.

Thanks for this info, I'll definitely take a look at these articles.

 

[kevnaff]

I'd say it is fairly common to use synthetic auto numbers rather than a natural key.
One reason that applies here is that numbers can be processed more efficiently than text and use less storage space. ("Code No" must be text for the leading zeros.)


"Code No" not being the primary key should not make any difference for any query. However, if "Code No" is required and unique (as a PK must be), the column in the database should also have these attributes to let the database engine know about these facts.

Hi Sonic,

[Code No] as far as I understood, should always be unique, however in the table it is set to Duplicates OK. I've no idea why this is the case.

As you showed, [Code No] is infact text, which solves my issue with the query, which is great.

Thanks for your help.

 

[GPGeorge]

Hi Sonic,

[Code No] as far as I understood, should always be unique, however in the table it is set to Duplicates OK. I've no idea why this is the case.

As you showed, [Code No] is infact text, which solves my issue with the query, which is great.

Thanks for your help.

Another name for "synthetic" key is "surrogate" key. You'll find a LOT of discussion on the topic under both synonyms, some of it quite heated. The point is that at least one field in a table needs to be unique and must reliably identify that RECORD as a whole. Whether there is an existing field which does that or not determines whether you need to consider a surrogate field. In some cases, a combination of fields is required to uniquely identify records. In such cases, that combination together, also known as a composite, is designated as the primary key.

Get yourself a cup of your favorite hot beverage and dig in. This is a fascinating, and highly critical, element of proper relational database application design.

 

[Pat Hartman]

STOP RIGHT NOW. You can't just change the PK of the table without knowing if there are relationships defined or how queries are joining to this table!!!!!!!!!!! And I can't believe you were told it was OK to just swap the PK without any investigation at all.

Before we get to how to resolve the issue, you need to understand that autonumbers are intended to provide unique identifiers when no other candidate key exists. If you have a candidate key but it is multiple columns or text, you will almost always use an Autonumber (surrogate) as the PK and make a unique index on the candidate Key that includes one or more fields to define uniqueness. Just because you use an Autonumber as the PK doesn't mean that that is what people will search on. They will search on either a text field like customer name or some more userfriendly code that you assign. Your Code No is a user generated code. without knowing anything about how it is generated, it seems to just be a sequential number so it should have been possible to use the Autonumber. But we won't go there now. It isn't relevant. Also , just to be clear, when you have relationships between tables they will always be on the PK of one table to a long integer in another table.. There will be occasions when you want to join tables on data field to data field but those cases will be when you are looking for bad data or working with imported data that doesn't contain your PK. A join in a query is NOT a relationship even though they look the same in the diagram. A relationship defined in the BE relationship window has the power to enforce Referential Integrity and keep certain kinds of bad data from ever being added to your tables. A join just allows you to connect two tables or queries and doesn't even have to make sense. you can create the join and the query will return some results but probably not what you expect. For example, SSN is a nine digit numeric code so is phone number. So, since the data types are the same (or compatable), you can create a query that joins SSN from the employee table to MainPhone on the company table. Does that make any sense? No, but you can do it. However, you would not be able to make a relationship because it is extremely unlikely that the set of data in the SSN column will match the set of data in the phone column.

To determine what if anything needs to be done:
1. Open the relationships window in the BE and look at the relationships. If there are none defined, that's your clue that the database was designed by someone who didn't understand relational databases and you should come back here after your issue is resolved and add them. If there are relationships, you should be able to determine how the tables are joined. If the join lines connect to EqupID, then the relationships are correct. If they join to the Code no, they are incorrect and RI cannot be enforced.
2. Open the FE and find the queries that reference this table. Access actually has a tool that helps with this. Look at the joins. What fields are connected? Are the joins linking to EquipID or to Code No? If they are joining to EquipID, the relationship has been defined correctly and should not be changed. If the joins ae to Code No, then the relationship is incorrect and you have work to do to resolve the issue.

Once you have determined what the developer thought of as the PK, you will know how to proceed. regardless BACK UP THE DATABASE before making ANY design changes. Make sure that no one is using the App at all. An easy way to ensure this is to go to the folder that holds the BE. as long as there is no .laccdb file, you can rename the BE to SAVEmydatabasename.accdb. That will ensure that if anyone opens the app before you are finished, they will get an error so send all the users an email warning.

Also, Go to the backstage Options and turn off Name Autocorrect. This is really important. If you don't turn it off, you will end up with a real mess on your hands. If you want it on and you actually understand how it works, you can turn it back on when you're done. Otherwise, it is best to leave this less than helpful feature off for safety.

1. If the joins are on code No,
--- set Code No to unique. If you get no errors, it is unique. If you get errors, it is not unique and you have to resolve the duplicates before you can continue because the PK MUST be unique. PERIOD.
--- Once Code No is unique, you can make it the PK and rename the autonumber field. When we're done, you can delete the field but for now, we want to make sure to break anything that uses it. If the Autonumber is not going to be the PK of the table, it shouldn't be there at all.

2. If the joins are all on EquipID, then there is no change that needs to be made. So, return to your backup and restore it.

 

[gemma-the-husky]

I have to say, in general I add an autonumber key irrespective of the user-friendly value that is required.
As in your case, especially where your "number" is actually a string, I think the autonumber is wholly positive.

There is no issue with linking tables/records based on the numeric autonumber PK, and showing users the actual part record number from the equipment table.

At the very least the autonumber would indicate the order in which the rows were added to the table, and users never even have to be aware of the existence of the numeric PK.

 

[gemma-the-husky]

Hi Sonic,

[Code No] as far as I understood, should always be unique, however in the table it is set to Duplicates OK. I've no idea why this is the case.

As you showed, [Code No] is infact text, which solves my issue with the query, which is great.

Thanks for your help.

If it's set to duplicates OK, then at some point the designer must have expected some values to be duplicated. (you would have thought)