for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available
Enforcing Referential Integrity for a relationship in a Microsoft Access database can avoid the loss or inadvertent updating of data records.
You can set Referential Integrity between two tables in Microsoft Access if the following are true -
When database tables are linked together, one table is usually called the Parent table and the other (the table that it is linked to) is usually called the Child. This is known as a parent-child relationship between Microsoft Access tables. Referential Integrity guarantees that there will never be an orphan, a child record without a parent record.
Referential Integrity operates strictly on the basis of the tables key fields; it checks each time a key field, whether primary or foreign, is added, changed or deleted. If a change to a key creates an invalid relationship, it is said to violate referential integrity.
When referential integrity is enforced (without enabling the cascading options), certain rules apply to the data. The following list gives some examples of this -
A CustomerID AutoNumber field in the Customers table is a unique Primary Key and can be related to a numeric field in the Orders table in a One-To-Many relationship.
You would not want to allow a user to enter any Order data for a Customer that does not have a record in the Customers table. Nor would you want to allow a user to alter the CustomerID field for a record in the Customer table, as this would break the link with related Order data for that Customer.
Deleting a Customer record that has matching records in the Orders table would also not be allowed.