Referential integrity problem

[orange]

If you have 2 "natural fields" that must be taken together to make a unique combination, you could create a composite primary key with this combination. A record has a single Primary key -- that key could be composite or atomic, but there is only 1.
If you wanted to use a single autonumber, Primary key, you could do so. But typically you would also include a composite unique index based on your combination of natural fields.

[ssanfu]

I know that orange has already responded, but I am going to reiterate: I use an autonumber type field as the PK field and would use two or more fields for a composite unique index to ensure uniqueness.

In a table (say tblIncidentsOverview), the PK field would be named "IncidentID_PK". (the "ID" in the name indicates it is a numeric type field)
If "tblIncidentsOverview" is linked to another table ("tblIncidentDetails"), the corresponding FK field wold be named "IncidentID_FK".
Again, this is my method when I design tables/relationships.



You might also read: Microsoft Access Tables: Primary Key Tips and Techniques

[csdlman]

Thank you all, this is very illuminating, helpful, and appreciated! While I'm still trying to see specific downsides of using natural primary keys, I am intrigued with the approach described above and will surely give it a try when an opportunity presents itself. Thanks!

[csdlman]

Ooops, one more question please regarding Lookup fields.

I've noted multiple respondents' aversion to Lookup fields. I assume this means that the Display Control for the *_FK fields in [tblIncidentDetails] would remain Text Box (and not Combo Box). Is that true? If so, would lookup functionality for users be handled somehow via form controls?

[CJ_London]

While I'm still trying to see specific downsides of using natural primary keys

there are very few 'natural' primary keys - car registration, VIN number, a membership or account number, NI numbers etc. The downside is these are a) still subject to change or correction b) may be subject to legal requirements of GDPR and c) usually text so are larger which reduces the efficiency of indexes. A long number (such as an autonumber) takes up 4 bytes, text is 1 byte per character plus 1. So any text of more than 3 chars will take up more space.

Why does that matter? - because of the way indexing works. A computer will read a block of data at a time - think it is 4096 bytes perhaps more. So when picking up an index, simplistically it will pick up 1024 indexed numbers at a time. If you have used text - say 8 characters in length it will pick up less than half this number - so statistically, it will take twice as long to find a record. This is less important when trying to avoid duplicates because the 'rule' is only applied at the time a new value is created. But will have an impact on all your other queries when linking records etc. Computers are much faster now so unlikely to have a noticeable affect a db with relatively few records - say 10k.

I assume this means that the Display Control for the *_FK fields in [tblIncidentDetails] would remain Text Box (and not Combo Box). Is that true? If so, would lookup functionality for users be handled somehow via form controls?

the control type generated (and other properties) when using a form wizard or by dragging a field onto a form are based on the properties of the underlying field, but easily changed to a combo when required.

[ssanfu]

Ooops, one more question please regarding Lookup fields.

I've noted multiple respondents' aversion to Lookup fields. I assume this means that the Display Control for the *_FK fields in [tblIncidentDetails] would remain Text Box (and not Combo Box). Is that true? If so, would lookup functionality for users be handled somehow via form controls?

You did see See "The Evils of Lookup Fields in Tables" in Post #2?

For me, it is just one of those things you NEVER do:
- Never begin object names with a number
- Never use spaces, punctuation or special characters (exception is the underscore) in object names
- Never use multi-Value fields
- Never use Calculated fields in tables (calculations are better done in queries)
- and Never (ever) use Look up FIELDS in tables.



There are only a couple of times a Look up FIELD *might* be useful:
- If it is a quick and dirty dB for your personal use.... well maybe. But users should Never have direct access to a table, so no help there.
- Using a form in datasheet view.

Personally, if I need a form that looks like a datasheet, I use continuous view and place controls where I want them (to *look* like a datasheet). I have better control (yes, I am a control freak), validation is easier and doing things like changing the BG color. (IMHO)
(but since I have never used a form in datasheet view... what do I know??)

[CJ_London]

There are only a couple of times a Look up FIELD *might* be useful:
- If it is a quick and dirty dB for your personal use.... well maybe. But users should Never have direct access to a table, so no help there.

I'll add one more (for me at any rate). That is where there are a limited and unchanging list of options that can be created using a value list - e.g. yes/no, days of the week, months of the year. Depending on requirement, usually a single column, but sometimes two e.g. yes/no may have -1,0 for the second (bound) column.

using forms as datasheets - yes do that sometimes because user needs some of the datasheet functionality such as column width/freezing/hiding/moving columns. However datasheets can be dangerous. If user is provided with a .accdb and deletes a column from the drop down menu - it also deletes the control from the form and if user answers yes to the closing 'do you want to save changes'....