Table Relationships
You can create relationships between your tables in a database diagram to show how the columns in one table are linked to columns in another table.
In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table called titles
that stores information about each book, such as the book's title, date of publication, and publisher. There is also information you might want to store about the publisher, such as the publisher's phone number, address, and zip code. If you were to store all of this information in the titles
table, the publisher's phone number would be duplicated for each title that the publisher prints.
A better solution is to store the publisher information only once in a separate table, publishers
. You would then put a pointer in the titles
table that references an entry in the publisher table.
To make sure your data is not out of sync, you can enforce referential integrity between the titles
and publishers
tables. Referential integrity relationships help ensure information in one table matches information in another. For example, each title in the titles
table must be associated with a specific publisher in the publishers
table. A title cannot be added to the database for a publisher that does not exist in the database.
For a better understanding of table relationships, see:
Types of Table Relationships
Overview of Referential Integrity
Types of Table Relationships
A relationship works by matching data in key columns — usually columns with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row , with an entry in the foreign key in the other table. For example, sales can be associated with the specific titles sold by creating a relationship between the title_id
column in the titles
table (the primary key) and the title_id
column in the sales
table (the foreign key).
There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.
One-to-Many Relationships
Many-to-Many Relationships
One-to-One Relationships
One-to-Many Relationships
A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the publishers
and titles
tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.
A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint .
The primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.
Many-to-Many Relationships
In a many-to-many relationship , a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table , whose primary key consists of the foreign keys from both table A and table B. For example, the authors
table and the titles
table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the titleauthors
table. The primary key of the titleauthors
table is the combination of the au_id
column (the authors
table's primary key) and the title_id
column (the titles
table's primary key).
One-to-One Relationships
In a one-to-one relationship , a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.
This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:
Divide a table with many columns.
Isolate part of a table for security reasons.
Store data that is short-lived and could be easily deleted by simply deleting the table.
Store information that applies only to a subset of the main table.
The primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.
Overview of Referential Integrity
Referential integrity is a system of rules that ensure relationships between rows in related tables are valid and that you do not accidentally delete or change related data.
When referential integrity is enforced, you must observe the following rules:
You cannot enter a value in the foreign key column of the related table if that value does not exist in the primary key of the related table. However, you can enter a null in the foreign key column. For example, you cannot indicate that a job is assigned to an employee who is not included in the
employee
table, but you can indicate that an employee has no assigned job by entering a null in thejob_id
column of theemployee
table.You cannot delete a row from a primary key table if rows matching it exist in a related table. For example, you cannot delete a row from the
jobs
** table if there are employees assigned to the job represented by that row in theemployee
** table.You cannot change a primary key value in the primary key table if that row has related rows. For example, you cannot delete an employee from the
employee
table if that employee is assigned to a job in thejobs
table.
You can set referential integrity when all of the following conditions are met:
The matching column from the primary table is a primary key or has a unique constraint .
The related columns have the same data type and size.
Both tables belong to the same database.
Enforced and Unenforced Relationships in Database Diagrams
Creating a relationship line in a database diagram automatically enforces referential integrity by creating a foreign key constraint on the related table. An enforced relationship appears in your database diagram as a solid line. For example:
An unenforced relationship, whose foreign key constraint is disabled, appears in your diagram as a dashed line. For example:
Depending on the features of your database, you can set options to disable the foreign key constraint for certain conditions, for example, during INSERT and UPDATE transactions.
To | See |
---|---|
Create relationships between database tables in a database diagram | Creating a Relationship Between Tables |
Ensure each value entered in a foreign key column matches an existing value in the related primary key column | Enforcing Referential Integrity Between Tables |
Link a column in a table with another column in the same table | Drawing a Single-Table Reflexive Relationship |
Create a many-to-many relationship | Mapping Many-to-Many Relationships to a Database Diagram |
Change the name of a relationship | Renaming a Relationship |
Remove the relationship between two tables | Deleting a Relationship |
Disable a foreign key constraint | Disabling a Foreign Key Constraint with INSERT and UPDATE Statements and Disabling a Foreign Key Constraint for Replication |