To understand Relational Database table-level or entity integrity, you must understand certain components of a database table. Table-level or referential integrity is managed by the database Jet engine, which establishes rules to maintain table relationships. Understanding table components, and their relationships, is fundamental to complying with the rules of integrity.
A primary key is the record field, or fields, that uniquely identify a specific record. A primary key must be unique to each record in a database and remain constant for the instance of that record. It cannot be a null value. The primary key can be a text value, though that is not recommended. It can also be a Microsoft Access auto-value, generated to be unique to each record. This Microsoft Access generated auto-value is called a unique index. Primary keys form the basis for relationships by providing the association between tables. They are also used to index and simplify queries.
A database relationship is the association between common fields, in two different tables. You must define table relationships within Microsoft Access. A relationship has a primary key table and a foreign key table. The table that contains a primary key is called the primary key table. The table, for which a primary key table forms a relationship, is called a foreign key table. A foreign key table contains a foreign key. A foreign key is the field, of the secondary table, that corresponds to the primary key of a primary key table. The primary key field and foreign key field should, ideally, have the same name, but it is not a requirement.
A relationship has both a parent and child. The primary key identifies the parent table in a parent-child relationship. Records, contained in the primary key table, are called parent records. A foreign key identifies the child table and child records are records, contained in the foreign key table. A foreign key must support every parent-child relationship. A relationship can be of the type one-to-one (1:1), one-to-many (1:M) or many-to many (M: M).
Integrity examines the state of relationships. Microsoft Access allows you to enforce referential integrity to ensure that relationships between tables are valid. However, you must, first, establish integrity between two open tables. In order to establish integrity between two tables, both tables must be stored in the same database or linked in Microsoft Access format, not external sources. A relational database table does not need to have a primary key. In order to establish integrity, however, at least one table must contain a primary key field. With the exception of text fields, the matching fields of a relationship must share the same data type and be the same size. Text fields need not have the same size. Though the rules of integrity do not require text fields to be the same size, size differences may lead to unpredictable results, when used in queries. The data type of auto-number fields is of the type long-integer.
If all of the above conditions are met, you must adhere to the following rules in order to enforce established integrity.
If any of these three rules are violated, Microsoft Access will produce an appropriate error message.