-2

I kindly request for a specialist help, probably a database designer or administrator.

I have been encountering this problem setting a referential integrity for two tables, setting the referential integrity is not the issue but the challenge I am having is that when I am done setting this referral integrity, it doen’t allow null values in the other table which implies that both tables should contain probably the same no. of rows that’ll make it both tables correspond

This is an example of exactly what I want to do:

  • I am creating tables, one named “Stock” which is meant for storing goods or accessories that are available in store and another table named “Customer_Details”
  • I want to set a field in both field (i. e. a field named “availability”) which will be a Yes/No data type, this field should be present in both field
  • Any time a customer is purchasing a particular accessory which is chosen from “Stock” table, it will automagically change the value of the field “availability” in the stock,

All I know and have been trying to do is to set the referential integrity in the relationship window, but unfortunately, this is not working out,

I kindly seek for someone who can put me through on how to go about this as I am new to access 2007.

  • It is called "referential integrity" not "referral integrity" – DavidPostill Mar 12 '15 at 8:18
0

"the challenge I am having is that when I am done setting this referential integrity, it doen’t allow null values in the other table"

Nul values are allowed, but there are restrictions:

However, you can enter a Null value in the foreign key. This specifies that the records are unrelated. For example, you cannot have an order that is assigned to a customer who does not exist. However, you can have an order that is assigned to no one by entering a Null value in the CustomerID field.


Source How to define relationships between tables in an Access database

Referential integrity

Referential integrity is a system of rules that Access uses to make sure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data. You can set referential integrity when all the following conditions are true:

  • The matching field from the primary table is a primary key or has a unique index.
  • The related fields have the same data type. There are two exceptions. An AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer, and an AutoNumber field that has a FieldSize property setting of Replication ID can be related to a Number field that has a FieldSize property setting of Replication ID.
  • Both tables belong to the same Access database. If the tables are linked tables, they must be tables in Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity cannot be enforced for linked tables from databases in other formats.

The following rules apply when you use referential integrity:

  • You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key. This specifies that the records are unrelated. For example, you cannot have an order that is assigned to a customer who does not exist. However, you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
  • You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the "Employees" table if there are orders assigned to the employee in the "Orders" table.
  • You cannot change a primary key value in the primary table if that record has related records. For example, you cannot change an employee's ID in the "Employees" table if there are orders assigned to that employee in the "Orders" table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.