Error re SQL server statement Foreign key conflict

[Kayleigh]

Hi there,

I am managing a MS Access DB coupled with SQL Server backend.

Occasionally users report that records go missing and I it may have to do with the following error although I am not clear on what it means and what to do about it. If anyone can enlighten me I would appreciate!

 

[tvanstiphout]

The insert statement conflicted with the foreign key constraint X.
A foreign key (FK) is usually a number that refers to a record in a related table. Simple example: the Northwind sample database Orders table has a CustomerID field. That is a FK that refers to a Customer record in the Customers table. If you try inserting an Order with a non-existing CustomerID, KABOOM, and for good reasons. After all, if you cannot look up the customer in the Customers table, then where are you going to send the products, and the invoice?
You need to tease apart the name of the FK, and it will tell you which relation in the database you are violating.
BTW, inserting that Customer record (to keep the example going) a millisecond later is not good enough. Insert the parent record (Customer) first, then insert the child record (Order).

 

[Pat Hartman]

1. Open the table in design view. Make sure that the default for ALL foreign keys is null NOT 0. MS made the decision to switch from null to 0 as the default for numeric columns a few versions ago. This helps novices because they don't know what they're doing but gets in the way of everyone else. There is some justification for making currency values default to 0 and some other numeric data types also but Long Integers are used almost exclusively as foreign keys and FK's should NEVER, EVER be allowed to default to 0. PERIOD.
2. Check the master/child links. Make sure the correct mapping is defined so that Access automatically inserts the FK when you add a record in the subform.

 

[Kayleigh]

Thanks for your suggestions @Pat Hartman

The tables are in SQL Server so how would I check it is Null not 0 in SSMS ?

Note - these are tables we have been using for many years without an issue!

 

[Pat Hartman]

Did you check the master/child links?

Telling you how to use SSMS is a pretty big task.
log in to your server
choose your database
get the tables list
right click on a table and chose design.
click on the field to see the definition.

You might also be able to see the defaults in Access by looking at the linked tables.