Referential integrity problem

[csdlman]

I am using Access 2013 from Office Professional Plus.



Access allows me to defeat referential integrity by appending space characters to a field value, and I'm semi-shocked. Details follow.

Table2 has two primary keys, one of which (Key1) looks up values in Table1.

There is an explicit one-to-many relationship between the Key1 fields in Table1 and Table2.

Join Property is 1 (only include rows where the joined fields from both tables are equal).

Enforce Referential Integrity is selected.

Cascade Update Related Fields is selected.

Cascade Delete Related Fields is not selected.

Key1 in Table2 is a Combo Box that looks up values in Table1.

Limit To List is set to Yes.

Various records are populated in Table 1 and Table2.

Manual attempts to update the Key1 value in a Table2 record to a value that does not appear in Table1 were rejected (The text you entered isn't an item in the list). This is what I expected.

Manual attempts to update a Key1 value in Table2 by appending a single space character to a value that appears in Table1 were rejected (The text you entered isn't an item in the list). This is what I expected.

An update query that appends a single space character to an existing Key1 value in Table2 was successful. No warning or rejection message was issued. The space character remains after closing and reopening the database, and after running Compact & Repair. I know it's whitespace, but it's a valid character that functions take note of. This is certainly not what I expected.

Is there something that I can do to prevent this from happening?

[davegri]

An update query that appends a single space character to an existing Key1 value in Table2 was successful.
Is there something that I can do to prevent this from happening?

Well, don't run that query!
Seriously, the TRIM function will remove extraneous leading and trailing spaces. Not sure if it will eliminate a lone space. There are other ways to clean your data so that a lone space is not allowed.

[kd2017]

Any possibility that the key in table 1 was also updated with the space because of Cascade update?

[CJ_London]

Not sure if it is a bug or not but just tried your exercise in A2010 and get the same result. It also allows the same update query to work on Table1 and any number of spaces - I suspect it still works because the underlying indexes will have ignored spaces.

As to prevent it from happening - you are writing the query, so don't do it, users should not be writing queries and the whole idea of primary/family keys is users do not populate them directly anyway - and if they are, use the trim function. Alternatively use autonumbers.

I would consider it very low risk unless you can come up with scenario where it isn't.

[orange]

Please show/tell us more about this

Table2 has two primary keys

I agree with davegri - if you are accepting values from input, you should have a routine to Trim leading and/or trailing spaces.

[ssanfu]

Table2 has two primary keys, one of which (Key1) looks up values in Table1.

Key1 in Table2 is a Combo Box that looks up values in Table1.

Sounds like Table2 has a composite (multi-field) primary key and one of the two fields is also a LOOK UP FIELD!
Not a good design IMHO.

[csdlman]

Well, don't run that query!
Seriously, the TRIM function will remove extraneous leading and trailing spaces. Not sure if it will eliminate a lone space. There are other ways to clean your data so that a lone space is not allowed.

The append query is not something I'm actively using. Its purpose was only to demonstrate the issue that I encountered. The trailing space crept into my data somehow, and I will take your advice regarding TRIM.

[csdlman]

No, the parent record in Table1 does not have the trailing space. Only the record in Table2 does.

[csdlman]

I agree, it is very low risk. I brought this up more as an alarming curiosity. I consider it a bug. I consider referential integrity sacrosanct.

[csdlman]

The foreign key in Table2 looks up values from the single primary key in Table1. There is no opportunity for the user to select anything for the foreign key in Table2 other than what is in Table1. The surprising thing that I've found is that an update query can be used to get an unrelated value into the Table2 foreign key, despite referential integrity being invoked, as well as limit to list.

[csdlman]

Table2 has two primary keys. One is a foreign key of Table1. I'd be interested in a short explanation of why this is not a good design and the negative things that can result from it. What alternative would you suggest?

[ssanfu]

Table2 has two primary keys. One is a foreign key of Table1. I'd be interested in a short explanation of why this is not a good design and the negative things that can result from it. What alternative would you suggest?

I am not sure about your design - haven't seen the dB, the design of the tables or a picture of the relationship window.

From what you have said and what I understand of your design, I wouldn't use that design.
All of my tables have an autonumber PK field.
I NEVER us Look up FIELDS in tables. (See "The Evils of Lookup Fields in Tables")
See "Microsoft Access Tables: Primary Key Tips and Techniques"

I re-read Post #1, tried to model the tables from the description and the result is that I am even more confused.

Care to post the dB??

[csdlman]

I know there is strong opposition to it from many, but I have always used meaningful key fields and not AutoNumber key fields. The database I described doesn't really exist. Its description is a simple distillation of the approach that I used in real databases.

I find that I can better track the roles my tables play by using meaningful key fields, and I have not run into any adverse situations, though I acknowledge that performance may not be optimized with my approach.

Given some time, I would be amenable to trying the AutoNumber primary key field approach to see how it compares in terms of complexity, maintainability, and performance.

[orange]

If you have a field in your set up that is unique, meaningful, never changing, then by all means use it as your Primary Key. This is often referred to as a "Natural key".

However, an autonumber (meaningless but unique number) is often used as a table's Primary Key. This is really intended for the DBMS requirement for uniqueness of the pk. It's possible that such a PK might never be displayed to the user. This concept is not specific to MS Access. Other DBMS(Oracle, MySql...) use a sequence which is used to value the PK.

Steve has already mentioned a general aversion to Lookup fields in Tables.

[csdlman]

In a table that called for two natural primary key fields, each record would have to have a unique combination of values in these two fields, though each field alone would not have to be unique. If one wanted to use a single AutoNumber primary key field instead of the two natural primary keys, by what means could the same uniqueness constraint be applied to the two natural (non-key) fields in Access?