Primary Key confusion

[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.

 

[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)