When the enforce referential integrity option is chosen, how does the black join line appear?

Access is a relational database software program in which you can create tables that are related or connected.

Taking time to plan a database is extremely important.  Creating a database with related tables takes even more consideration.  When planning a table, take time to determine how to break down the required data and what relationships need to be defined to eliminate data redundancies.

One idea to help you determine what tables are necessary in a database is to think of the word about.  A table should be about only one subject.  They can be about customers; about products; about suppliers.

Along with determining the necessary tables for a database, you need to determine the relationship between tables.

The ability to relate or "join" tables is what makes Access a relational database system.

Generally, one field in a table must be unique so that one record can be distinguished from another. A field with a unique value is considered a primary key field.

A table can have only one primary key field and it is the field by which the table is sorted whenever it is opened.

In a field defined as a primary key field, duplicate values are not allowed. Access also expects a value (cannot be blank) in each record in the primary key field.

When a new record is added to a table, Access checks to ensure that there is no existing record with the same data in the primary key.  If there is, an error message displays indicating that there are duplicate values and does not allow the record to be saved.

When adding a new record, the primary key field cannot be left blank.  Access expects a value in each record in the table.  If a value is not entered in a field, Access enters a null value.  A null value cannot be given to a primary key field.  Access will not let you close a database containing a primary key field with a null value.

By default, Access includes an ID field as the first field in a new table, assigns the AutoNumber data type, and identifies the field as the primary key.  You can use this default field as the primary key or define your own.

To define a primary key field, open a table and then click the View tab to switch to Design View.  To add or remove a primary key from a field, click the desired field in the Field Name column and then click the Primary Key button in the Tools group on the Table Tools Design tab.  A key icon is inserted in the field selector bar for the desired field.

Typically, a primary key field in one table becomes the foreign key field in a related table.

Data in a foreign key field must match data in the primary key field of the related tables.

In Access, one table can be related, which is generally referred to as performing a join.

When tables that have a common field are joined, you can extract data from both tables as if they were one large table.

At the Relationships window, click the Show Table button to display the Show Table dialog box

You can create a one-to-many relationship between tables. In this relationship, a record must be added to the “one” table before it can be added to the “many” table.

In a one-to-many relationship, the table containing the "one" is referred to as the primary table and the table referred to as the "many" is referred to as the related table.

You can create a one-to-one relationship between tables in which each record in the first table matches only one record in the related table. This type of relationship is generally used when you want to break a large table with many fields into two smaller tables.

Use the mouse to drag the common field (fields with the same name) from the primary table's field list box (the primary key field) to the related table's field box.

Once joined, a black join line will attach the field list boxes--with the number 1 (signifying the "one" side of the relationship) from the primary key field and the symbol that represents infinity to the related table (signifying the "many" side of the relationship).

The black line, called the join line, is thick at both ends if the Enforce Referential Integrity option is chosen.  If this option is not chosen, the line is thin at both ends.

Access follows a set of rules that provide referential integrity--which enforces consistency between related tables.  The referential integrity rules ensure that a record added to a related table has a matching record in the primary table.

To print table relationships, display the Relationships window, click the Relationship Report button, click the Print button on the Print Preview tab, and then click OK at the Print dialog box.

When a relationship is established between tables, you can view and edit fields in related tables with a subdatasheet.

To display a subdatasheet for a record, click the expand indicator (plus symbol) that displays at the beginning of a (to the left) record.

To display subdatasheets for all records, click the More button in the Records group on the Home tab, point to Subdatasheet, and then click Expand All.

Display the Insert Subdatasheet dialog box by clicking the More button in the Reports group on the Home tab, pointing to Subdatasheet, and then clicking Subdatasheet.

Turn off the display of a subdatasheet by clicking the collapse indicator (minus symbol) at the beginning of the record.

To turn off the display of subdatasheets for all records, click the More button, point to Subdatasheet, and then click Collapse All.

Remove the connection between tables by clicking the More button, point to Subdatasheet, and then click Remove.