Cannot add or change a record because a related record is required in table 'COMPANY'

Question

I think my Contact List database design is correct, but there is an error. Access 2013 is telling me:

You cannot add or change a record because a related record is required in table 'COMPANY'

I'm new to Access and this is for a class called "Computer Concepts II". I've already done my assignment, and followed the instructions meticulously. This course is held online only and I have tried 3 times in 3 different ways but haven't been able to get any feedback from the instructor.

Here is the breakdown of the things I have tried:

• As the error seemed to indicate, I attempted to fix the issue by closing the PERSON table and then opening and entering data in the FAMILY table. I made up 3 families is different states, with my real address and surname entered as the 4th. Saved FAMILY table.
• Opened PERSON table again and began to enter data from left to right (screenshot). Upon using arrow down key or double-clicking to autofit column widths I got the the error at the beginning, referencing the COMPANY table.

• Found and read the following seemingly related posts, but none of them offer the help I was looking for:

  "You cannot add or change a record because a related record is required", but related record exists?

  You cannot add or change a record because a related record is required in table

  Not allowed to create or change a record in MS Access

  • “You cannot add or change a record because a related record is required”, but related record exists?

  • You cannot add or change a record because a related record is required in table

  • Not allowed to create or change a record in MS Access

Business Rules

1. A person must have only one family, but a family can have many people.
2. A person can work for one or zero companies.
3. A company can have zero or many people.
4. For MS Access purposes PERSON is the strong table.

MY ERD Diagram

BUILT THE TABLES

1. Built PERSON table.
2. Built COMPANY table.
3. Built FAMILY table.

CREATED RELATIONSHIPS

1. Created relationships between tables (see screenshot attached). For optionality, set PERSON as the "strong" table.
2. Originally had ID-FAM and LNAME fields in FAMILY table as both required and indexed.
3. Originally had ID-PER, ID-FAM_FK, & ID-COM_FK in PERSON table as indexed.
4. Originally had ID-COM as the only indexed and required field in COMPANY table.

ENCOUNTERED 'RELATED RECORD' ERROR WHILE ENTERING DATA IN PARENT TABLE

• As indicated in the Using Access 2010 document, I started by entering data in my parent table (PERSON) first. However, when I finished filling all fields in the first row and pressed the "ARROW DOWN" key to advance to the next row, I got an error message saying

"You cannot add or change a record because a related record is required in table 'FAMILY'"

Well, a COMPANY entry is completely optional and not required in any way, so that's a bit confusing. I'm not sure what I'm doing wrong or where to go from here. I did not know how to use Access before this class but I think I have a pretty good handle on it now, and I've followed the instructions meticulously. Thus, I believe the issue is a misconfigured setting somewhere.

Answer 1

There should be no value in the Default property field.

Access was automatically setting a default value for my foreign keys of zero ("0"), even though a person wasn't required to have a company. "0" in the company field for my person record was causing the error.

I suspect that the database was looking for a company record with an ID of "0" and because my company records start at 2 the "0" record was never found, causing the error.

The solution is to go into the default field for my 2 foreign keys in the design view, delete the zeros, and make sure there are no default values. In other words, ensure there are no default values for foreign keys.

I was able to add 3-4 rows of data for each table and run a basic query to test the database. It seems to be working.

Answer 2

If you use an input mask for the child table, Access doesn't recognize the record you put in the parent table even if the record is in accord with the input mask of the child table.

Answer 3

Go to Database, then Edit Relationship that is in between two related tables. Check "Cascade Update Related Field". Now an update or edit will work and no need to update anything else.

Answer 4

Declaring a FK in a table means that subrow value for its columns in that table must appear in the referenced table. There is no other reason to declare a FK. In SQL databases when a FK column holds a NULL then (by default) the subrow value does not have to appear in the referenced table.

So the non-blank/non-NULL FK values of added or edited Person row must already be in some rows of their referenced tables. In a comment you say a person has to have a family, ie that Person FK is required, ie can be non-blank/non-NULL, but doesn't have to have a company, ie that Company FK is non-required, ie can be blank/NULL. So to add a Person row you must first have a row for their family in Family, then add the Person row with their family's ID-FAM value as ID-FAM-FK and with ID-COM-FK either blank/NULL or the ID-COM value in their company's row in Company.

The error message is not very clear. The actual violation is that before you can leave Person having added or edited rows each row must have a value for every required FK, and hence a row it is referencing, and either that or NULL for every non-required FK. But the error message just mentions the part about an adequate row being missing.

FKs (and other constraints) are not needed to query or update a database, including JOINing. They are for update validation (and enhanced optimization) by the DBMS.

Answer 5

If you don't need to link family and/or company to Person record, do not enter anything to ID-FAM_FK and ID-COM_FK fields, leave them blank. In table definition those fields should not have default value and Required property should be No.

If you need to link Person with Family and/or Company, enter data to Family and/or Company tables first, then add or update record in Person table