Mandatory FK values? "You cannot add or change a record because a related record is required in table" (1 Viewer)

[riktek]

This is a new one.

I'm encountering the error "You cannot add or change a record because a related record is required in table [X]".

The first question is why would this be? In my experience, FK field values are optional, so this comes as a bit of a surprise.

The second question is what can I do to avoid this behavior?

The case is this: A newly developed database has two tables related by non-PK fields in each. The lookup table is populated with records and the main table is not. The PK fields of both tables are AutoNumber Long. The FK fields of both tables (by which the two tables are joined) are text, length 255, AllowZeroLength = No, and Required = No.

The populated table's field indexed without duplicates. Access would not create the relationship otherwise. The empty table is indexed with duplicates OK. The relationship is defined to enforce referential integrity. Access presumes to assign the respective "one" and "many" sides but does so as it would any other lookup table.

Under a seemingly identical configuration (albeit to the lookup table's PK, and by fields typed Long), FK values are optional.

A third question is whether changing the lookup table's PK field (while retaining the AutoNumber field, indexed for no duplicates) would make a difference.

Any thoughts would be greatly appreciated.

 

[theDBguy]

I am guessing the error is showing up due to referential integrity is enabled. Just as a test, see if the error goes away if you temporarily turn RI off.

 

[Gasman]

The relationship is defined to enforce referential integrity

 

[gemma-the-husky]

If you have a table of customers and a table of orders.

Each order will have a field to show the customer ID. You set the RI to enforce this behaviour.

Now if you add a new order, the order must contain a valid customer ID cross reference, or you will get the error you saw. In some cases Access can automatically add the cross reference, such as if you are using an orders subform, where you have declared the subform as being related by the customer ID.

You also won't be able to delete a customer if you have existing orders, as that would leave orphan orders.

Also you won't be able to set the RI between tables if existing data has records that don't match. RI is very useful for keeping your data well structured.

 

[CJ_London]

FK field values are optional, so this comes as a bit of a surprise.

Assuming you have defined the relationship then the FK field has to have a matching parent, or is set to null - which is not the same as a zls - your description does not say, only that you do not allow zero length

 

[MajP]

One problem is now a numeric fk defaults to zero unless you change it. Do not know why they just do not set the default to null.

 

[gemma-the-husky]

Yes, but unless there's special circumstances, I don't believe you would really want a record in a sub table without the reference to the primary key populated.

Although I'm sure some of the good people here could give an example.

 

[GPGeorge]

This is a new one.

I'm encountering the error "You cannot add or change a record because a related record is required in table [X]".

The first question is why would this be? In my experience, FK field values are optional, so this comes as a bit of a surprise.

The second question is what can I do to avoid this behavior?

The case is this: A newly developed database has two tables related by non-PK fields in each. The lookup table is populated with records and the main table is not. The PK fields of both tables are AutoNumber Long. The FK fields of both tables (by which the two tables are joined) are text, length 255, AllowZeroLength = No, and Required = No.

The populated table's field indexed without duplicates. Access would not create the relationship otherwise. The empty table is indexed with duplicates OK. The relationship is defined to enforce referential integrity. Access presumes to assign the respective "one" and "many" sides but does so as it would any other lookup table.

Under a seemingly identical configuration (albeit to the lookup table's PK, and by fields typed Long), FK values are optional.

A third question is whether changing the lookup table's PK field (while retaining the AutoNumber field, indexed for no duplicates) would make a difference.

Any thoughts would be greatly appreciated.

There are two ways to interpret the claim that a value in a Foreign Key field is optional.

One, the way I would normally expect to see it, would be that a record in the referencing table need not have a value for that field, depending on reality. So, to conjure up an example, let's say I have a table of vehicles which includes a Foreign Key to say "LoanType" to indicate whether a loan on the vehicle is from a credit union, a bank, or a private lender. That's a bit of a stretch, no doubt, but the best I can come up with at the moment. Not all vehicles would have a loan, they'd be owned outright. In that case, there is no "LoanType", so that might be considered an optional case, and Null would be the appropriate value, as @MajP suggested.

The other way to interpret this, based on the problem description, might be that the value in that field could be one that doesn't exist in the referenced table. At least, that's how it appears from the question. If it's optional, then anything goes. However, that falls outside the restrictions of what happens when Referential Integrity is enforced, i.e. when there is an actual Foreign Key constraint on the values in that field.

Therein lies the problem being encountered.

You do want to enforce RI, because "anything goes" would create random, unreliable data.
You could allow nulls, or not allow nulls, in that field depending on the business rules that must be enforced.

The way to avoid this behavior is to not allow data entry to violate RI here.

This statement also raises some additional questions.

"A newly developed database has two tables related by non-PK fields in each. " Unfortunately, I can't think of a way that that would be a valid description of a relationship. Please elaborate on what that actually looks like.

 

[MajP]

would have a loan, they'd be owned outright. In that case, there is no "LoanType", so that might be considered an optional case, and Null would be the appropriate value, as @MajP suggested.

My bigger point here is that the default for a nueric fields is 0 and that is almost never appropriate if that field is used as an FK. Why MS does this is just dumb. It causes more problems than helps. Since most time we are relating to a PK autonumber that can never be zero. So if you enforce referential integrity and choose to leave the FK blank it will default to zero and give you an error.
The even bigger problem is that most of the times we pick FKs with a combo. It appears as if it is blank because 0 is not in the rowsource, but in fact there is a zero. @Pat Hartman said this is newer behavior and in past it was null. I do not remember, but can cause the problem as described because this "hidden" zero is not in the parent table.

 

[GPGeorge]

Agreed and enthusiastically supported.

I think, based on nothing more than intuition and healthy skepticism, that the default of 0 was left that way because of reasons.

IIRC correctly, originally the default was 0, followed by one or two versions in which it was Null, followed by a reversion to the 0 again, for reasons.

 

[Pat Hartman]

The defaults for numeric fields have vacillated over time. At one point they defaulted to null but now the default is zero. I believe they went with the zero because new developers (and even some experienced ones) do not understand how to handle null values. This is a case where it would have been better to split the baby. Long Integers are rarely used for arithmetic and usually are foreign keys. So, I would be less unhappy with long integers defaulting to null but other numeric types defaulting to zero.

The other problem default is Allow Zero Length Strings. There is no way to visually distinguish a ZLS from a null. So if your definition allows ZLS, they can creep in accidentally. One way is a fixed width import file (although this might actually be fixed). The other way is by improperly clearing a text control on a form. The correct way to clear a textbox is to either use esc to clear your entry or select the text and use the delete key. Clearing the field by backspacing results in ZLS unless they've fixed that too.

Another problem with allowing ZLS is that if you set the field to required, a ZLS is a character and so the field can be left "blank" .

 

[riktek]

Assuming you have defined the relationship then the FK field has to have a matching parent, or is set to null - which is not the same as a zls - your description does not say, only that you do not allow zero length

I actually did say, actually: The main table has no records. So, any and every record will start with a null FK value because AllowZLS = No.

 

[riktek]

I am guessing the error is showing up due to referential integrity is enabled. Just as a test, see if the error goes away if you temporarily turn RI off.

That's true, of course, and is the case. No reason for the problem, even still.

What ended up working was deleting the relationship another table had with the lookup table. I have no end of tables sharing a lookup table in other contexts, so this explains nothing. Re-creating this second relationship characterizes it as 1:1 instead of M:1 as it was before (and the problem also no longer exists). Curiouser and curiouser.

 

[riktek]

There are two ways to interpret the claim that a value in a Foreign Key field is optional.

One, the way I would normally expect to see it, would be that a record in the referencing table need not have a value for that field, depending on reality. So, to conjure up an example, let's say I have a table of vehicles which includes a Foreign Key to say "LoanType" to indicate whether a loan on the vehicle is from a credit union, a bank, or a private lender. That's a bit of a stretch, no doubt, but the best I can come up with at the moment. Not all vehicles would have a loan, they'd be owned outright. In that case, there is no "LoanType", so that might be considered an optional case, and Null would be the appropriate value, as @MajP suggested.

The other way to interpret this, based on the problem description, might be that the value in that field could be one that doesn't exist in the referenced table. At least, that's how it appears from the question. If it's optional, then anything goes. However, that falls outside the restrictions of what happens when Referential Integrity is enforced, i.e. when there is an actual Foreign Key constraint on the values in that field.

Therein lies the problem being encountered.

You do want to enforce RI, because "anything goes" would create random, unreliable data.
You could allow nulls, or not allow nulls, in that field depending on the business rules that must be enforced.

The way to avoid this behavior is to not allow data entry to violate RI here.

This statement also raises some additional questions.

"A newly developed database has two tables related by non-PK fields in each. " Unfortunately, I can't think of a way that that would be a valid description of a relationship. Please elaborate on what that actually looks like.

Good thoughts and questions.

Initially, this is more the "Loan Type" scenario you describe.

Furthermore, the main table has no records at all. So, this isn't a question of it having records with FK values not matching those in the lookup table. Its FK field has AllowZLS = No, so that field will be Null or a value from the lookup table and never anything else. I understand Null handling and was quite deliberate about this configuration, and was careful also to mirror it in the other table's field.

And, RI is essential, of course.

As for the FK to FK relationship, the FK in the main table is unremarkable and indistinguishable from any other case of a FK. Joining to the lookup table's FK may seem odd at first but, as noted, this FK field contains unique values for each of its records. Furthermore, it is indexed accordingly. This is imported (and un-normalized) data and I chose to define an AutoNumber field as PK because it is computationally more efficient. Still, the indexed FK having unique values is tantamount to a PK and functionally equivalent.

I've actually found FK joins to be quite useful in some situations, usually in the context of linked subforms.

 

[riktek]

My bigger point here is that the default for a nueric fields is 0 and that is almost never appropriate if that field is used as an FK. Why MS does this is just dumb. It causes more problems than helps. Since most time we are relating to a PK autonumber that can never be zero. So if you enforce referential integrity and choose to leave the FK blank it will default to zero and give you an error.
The even bigger problem is that most of the times we pick FKs with a combo. It appears as if it is blank because 0 is not in the rowsource, but in fact there is a zero. @Pat Hartman said this is newer behavior and in past it was null. I do not remember, but can cause the problem as described because this "hidden" zero is not in the parent table.

I actually had been thinking along these lines and by experimenting with adding a null record to the lookup table. Alas, no dice.

Agreed that 0 as default for numeric fields is inappropriate. I've gotten stung on this with FK fields a few times. Deleting the "0" from the field's DefaultValue property, and that value from all records, was the fix. The default then is Null, of course, just as it is with Text fields.

 

[riktek]

The defaults for numeric fields have vacillated over time. At one point they defaulted to null but now the default is zero. I believe they went with the zero because new developers (and even some experienced ones) do not understand how to handle null values. This is a case where it would have been better to split the baby. Long Integers are rarely used for arithmetic and usually are foreign keys. So, I would be less unhappy with long integers defaulting to null but other numeric types defaulting to zero.

The other problem default is Allow Zero Length Strings. There is no way to visually distinguish a ZLS from a null. So if your definition allows ZLS, they can creep in accidentally. One way is a fixed width import file (although this might actually be fixed). The other way is by improperly clearing a text control on a form. The correct way to clear a textbox is to either use esc to clear your entry or select the text and use the delete key. Clearing the field by backspacing results in ZLS unless they've fixed that too.

Another problem with allowing ZLS is that if you set the field to required, a ZLS is a character and so the field can be left "blank" .

A default value differs from a blank value, to be clear.

A default value follows from what's expressed in the field's DefaultValue property. Delete the "0" for numeric fields and the field will be Null for new records.

AllowZLS doesn't set the default value of text fields to "", it just says "" can be assigned to the field, or that the field may contain it if a pre-existing value is deleted. Text fields for new records initialize to Null regardless.

Thoroughly in agreement that the "0" default is indefensible, however.

 

[theDBguy]

That's true, of course, and is the case. No reason for the problem, even still.

What ended up working was deleting the relationship another table had with the lookup table. I have no end of tables sharing a lookup table in other contexts, so this explains nothing. Re-creating this second relationship characterizes it as 1:1 instead of M:1 as it was before (and the problem also no longer exists). Curiouser and curiouser.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

AllowZLS doesn't set the default value of text fields to "", it just says "" can be assigned to the field, or that the field may contain it if a pre-existing value is deleted. Text fields for new records initialize to Null regardless.

I didn't say that the AllowZLS set the default. I merely stated that a ZLS is "something" and therefore if you want the field to be required, then you CANNOT leave AllowZLS as yes because it is not as hard as you think to get a ZLS into a field. And most people would be surprised that their "required" didn't work when they saw the "empty" field.

Thoroughly in agreement that the "0" default is indefensible, however.

It isn't indefensible for most numeric fields. Only the long integer causes a real problem. It is a problem though if you append incomplete rows. For example. You append a record for each student who will take a test. Then when the tests are scored, you enter the value. Having a 0 in the score, even temporarily, could cause a rash of phone calls from parents asking why their child's grades dropped. Because the average of (75,0,75) = 50 but the average of (75, null, 75) = 75. Zero has meaning. Did the child earn the zero or was the score simply not yet entered?

Personally, I always remove the 0 as the default for numeric fields and I always change the setting of AllowZLS to no. Here's a sample database with helpful code. That way, if a value is required, I can ensure that the user enters something. Additionally, knowing that a field either is null or has something, simplifies logic in queries since I don't have to consider whether the field is text or numeric. Both will be null if empty. NO ZLS allowed

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[GPGeorge]

A default value differs from a blank value, to be clear.

A default value follows from what's expressed in the field's DefaultValue property. Delete the "0" for numeric fields and the field will be Null for new records.

AllowZLS doesn't set the default value of text fields to "", it just says "" can be assigned to the field, or that the field may contain it if a pre-existing value is deleted. Text fields for new records initialize to Null regardless.

Thoroughly in agreement that the "0" default is indefensible, however.

It's not useful to talk about "blank" values in the context of a field in a database table. "Blank" obscures the fact that the stored value can be either a ZLS or Null. Blank refers to the resulting visual appearance, whereas ZLS and Null refer to the value stored in the field. For that reason, I strongly urge database developers to keep references to "blanks" or "blank values" to a minimum.

 

[riktek]

it is not as hard as you think to get a ZLS into a field

I haven't done a study, per se, but it is fairly easy.

most people would be surprised that their "required" didn't work when they saw the "empty" field

This nuance isn't something that most people would expect. Null handling is a bit arcane for beginners and it may not occur to them to think what the test is for whether "Required" is met.

Only the long integer causes a real problem.

This is true. My statement was overly broad. The issue arises, as you note, with numeric data (as opposed to index) fields. There is a big difference between 0 and Null, not only when Johnny's parents get his report card, but also in doing any kind of statistical analysis. An average will include any zero but not any null, for example.