Developers create a relationship between two tables when they want to associate rows of one table with rows of another. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create relationships between tables, they should follow the next steps:
1. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2. From the Table Designer menu, click Relationships.
3. In the Foreign-key Relationships dialog box, click Add.
The relationship appears in the Selected Relationship list with a system-provided name in the format FK_<tablename>_<tablename>, where tablename is the name of the foreign key table.
4. Click the relationship in the Selected Relationship list.
5. Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.
6. In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.
7. In the grid beneath, choose the columns contributing to the table’s primary key. In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.
Table Designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.
8. Choose OK to create the relationship.
Note: The columns chosen by developers for the foreign key must have the same data type of the primary columns they correspond to. There must be an equal number of columns in each of the keys.