Table relationship (one-to-one, one-to-many, multiple-to-many)

You can create relationships between tables in a database chart to display how columns in one table are linked to columns in another table.

In a relational database, the use of relationships can avoid unnecessary data. For example, if you want to design a database that can track book information, you need to create a database namedtitlesIt is used to store information about each book, such as the title, publication date, and publishing house. You may also save information about the Publishing House, such as the phone number, address, and zip code of the publishing house. If you wanttitlesIf the table stores all this information, the phone number of the publishing house will be repeated for each book published by a publishing house.

A better way is to separate the information about the publishing house in a separate table,publishers. Then you cantitlesPlace a pointer to a certain item in the publishing table.

To ensure your data synchronization, You can implementtitlesAndpublishersIntegrity. The reference integrity relationship helps ensure that the information in one table matches the information in another table. For example,titlesEach name of the table must bepublishersA specific publisher in the table. If there is no publisher information in the database, the title of the publisher cannot be added to the database.

To better understand the table relationship, see:

• Define table relationships

• Implementation integrity of reference

Define table relationships

To establish a link, you must match the data in the key column (usually the columns with the same name in the two tables ). In most cases, this relationship matches the primary key of a table (which provides a unique identifier for each row) with an item in the external key of another table. For exampletitlesTabletitle_id(Primary Key) andsalesTabletitle_idThe Relationship Between Columns (external keys) is associated with the specific name of the sold book.

There are three relationships between tables. The type of the created link depends on how the related columns are defined.

• One-to-multiple relationship

• Many-to-many relationship
• One-to-one relationship

One-to-multiple relationship

A one-to-multiple relationship is the most common one. In this relationship, a row in Table A can match multiple rows in Table B, but one row in Table B can only match one row in table. For example,publishersAndtitlesThere is a one-to-many relationship between tables: each publisher publishes many books, but each book name can only come from one publisher.

A one-to-multiple relationship can be created only when a related column is a primary key or has a unique constraint.

Many-to-many relationship

In the many-to-many relationship, a row in Table A can match multiple rows in Table B, and vice versa. To create this relationship, you need to define the third table, called the combined table. Its primary key consists of the external keys of table A and table B. For example,authorsAndtitlesTables have many-to-many relationships, because these tables aretitleauthorsA table has a one-to-multiple relationship.titleauthorsThe table's primary key isau_idColumn (authorsTable Primary Key) andtitle_idColumn (titlesTable primary key.

One-to-one relationship

In a one-to-one relationship, one row in Table A can only match one row in Table B, and vice versa. If the related columns are both primary keys or all have unique constraints, you can create a one-to-one relationship.

This relationship is not common, because in general, the information related to this method is in a table. You can use the one-to-one relationship:

• Split a table with multiple columns.

• Isolate part of the table for security reasons.
• Save temporary data, and you can easily Delete the data by deleting the table.
• Save information that only applies to the subset of the master table.

Implementation integrity of reference

Integrity of reference is a rule system that ensures the validity of the relationship between related table rows and does not inadvertently delete or change the relevant data.

The following rules must be observed when reference integrity is implemented:

• If no value exists in the primary key of the table, the value cannot be entered in the external key column of the table. However, you can enter a null value in the external key column. For example, you cannot assign a job to a job that is not included inemployeeEmployees in the table, but canemployeeTablejob_idEnter a null value in the column, indicating that an employee is not assigned work.

• If a row has matched rows in the relevant table, the row cannot be deleted from a primary key table. For exampleemployeeThe table indicates that some employees are assigned a job and cannotjobsDelete the row corresponding to the job in the table.
• If a row in the primary key table has a related row, you cannot change the value of a key in the primary key table. For example, if an employee is assignedjobsA job in a table cannot beemployeeDelete the employee from the table.

You can set the integrity of the reference only when all of the following conditions are met:

• The matching column in the primary table is a primary key or has unique constraints.

• Related columns have the same data type and size.
• The two tables belong to the same database.