Working with 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 details about working with relationships, see the following topics.
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
In This Section
Table Relationships (Visual Database Tools)
Provides a brief overview of relationships as a means to prevent redundant data.Types of Table Relationships
Describes one-to-one, one-to-many, and many-to-many relationships.Overview of Referential Integrity
Describes requirements for enforcing referential integrity and the effect this has on database users.How to: Change Relationship Properties
Provides steps for changing properties for relationships.How to: Create Relationships Between Tables
Create relationships between database tables in a database diagram.How to: Delete Relationships
Provides steps for removing the relationship between two tables.How to: Check Existing Data when Creating a Relationship
Provides steps for how to check existing data when creating a relationship.How to: Show Relationship Properties
Provides steps for accessing properties for relationships.
Reference
Reference (Visual Database Tools)
Provides links to general reference topics covering queries, operators, search conditions, user interface reference topics, and error message topics.Foreign Key Relationships Dialog Box
Describes the various options for creating and modifying foreign key relationships.