Referential integrity is a set of rules. If you update data in a database then use referential integrity for access enforces to maintain consistency between related tables. It simply means data accuracy.
• In referential integrity, primary and foreign key relationships between tables are enforced in a database. It is used to maintain validated database.
• Each foreign key value of the referencing table must have the matching value in primary key field of referenced table.
• When you add a new table in database, you can add that table in relationship and enforce referential integrity.
Steps to create referential integrity
• After creating tables in database, click database tools tab on the ribbon. Select relationships under relationships group.
• Right click in relationships workspace and select show table. Show table dialog box opens and select table and add it by clicking add.
• Double click in any field of table; edit relationship dialog box opens in that click create new.
• In create new dialog box, select table name and column name whichever you want in relationship and click ok.
• Click the check box of enforce referential integrity in edit relationship dialog box opened and click create.
Rules of referential integrity
• It does not allow you to delete record from primary table when it has matching value in related table.
• It allow you to delete record from related table though it has matching value in primary table
• It does not allow you to add or change foreign key value in related table when it does not have matching value in primary key field of primary table.
• It does not allow changing values in record of primary table when it has matching value in related table.
Consider two tables are Customer and Order. Enforce referential integrity between two tables.
• Customer has fields such as CustomerID, CustomerName and CustomerAddress.
• Order has fields such as OrderID, CustomerID, and Amount.
Primary table: Cust o mer
Related table: Order
• Any of the records in customer table cannot be deleted. The reason is that all have matching value in order table.
• Any record in order table can be deleted though it has matching value in Customer table.
• You cannot add record like this in order table, because it does not have CustomerID is 104 in customer table.
• You cannot change records in Customer table when it does not have matching records in order table such as consider third record CustomerID is 102 cannot be changed as 112 and Ellina cannot be changed as Thomas.
Database Management System is a useful tool, if we create and design a database carefully as per the needs of user. When you design a database, need to follow some guidelines.
The following are the some Guidelines for designing Databases:
• Identify all the fields needed to produce the required information.
• Organize each piece of data into smallest useful part.
• Group related fields into tables.
• Determine each table's primary key.
• Include a common field in related tables.
• Avoid data redundancy.
There are only 3 types of fields we can assign field size.
It allows us to enter field values like characters, spaces, digits and special characters. Generally this data type is used in many tables for names, addresses, e-mails etc.
This data type allows us to enter 0 to 255 characters.
This data type allows us to enter field values of positive and negative numbers. It allows special characters like commas and decimal points for calculation purpose. This data type specifically used for calculation purpose.
This data type allows 1 to 15 digits.
This data type consists of positive integer values assigned automatically by access database every time when a new record is created.
By using this data type, we can specify either random or sequential numbering which gives a unique field value. So we can treat this field as primary key field.
This data type allows up to 9 digits.