Export (0) Print
Expand All
Expand Minimize

Foreign Key Relationships Dialog Box (Visual Database Tools)

Use this dialog box to create or modify relationships between tables in your database. In a foreign key relationship, FOREIGN KEY constraints work with PRIMARY KEY or UNIQUE constraints to enforce referential integrity among specified tables. For example, you can place a FOREIGN KEY constraint on the ProductID column in the Order_Details table to ensure that a value entered in that column matches an existing value in the ProductID column of the Products table.

ms177288.note(en-US,SQL.90).gifNote:
If the table is published for replication, you must make schema changes by using the ALTER TABLE Transact-SQL statement or SQL Server Management Objects (SMO). When schema changes are made using the Table Designer or the Database Diagram Designer, it attempts to drop and re-create the table. You cannot drop published objects; therefore, the schema change will fail.

To access this dialog box, open the table definition for the table with the foreign key, right-click the table definition grid, and click Relationships.

Selected Relationship

Lists existing relationships. Select a relationship to show its properties in the grid to the right. If the list is empty, no relationships have been defined for the table.

Add

Create a new relationship. The Tables and Columns Specifications must be set before the relationship will be valid.

Delete

Delete the relationship selected in the Selected Relationships list. To cancel the addition of a relationship, use this button to remove the relationship.

General Category

Expand to show Check Existing Data on Creation or RE-Enabling and Tables and Columns Specifications.

Check Existing Data on Creation or Re-Enabling

Verify all existing data in the table before the constraint was created or re-enabled, against the constraint.

Tables and Columns Specifications Category

Expand to show which columns from which tables act as the foreign key and primary (or unique) key in the relationship. To edit or define these values, click the ellipsis button () to the right of the property field.

Foreign Key Base Table

Shows which table contains the column acting as a foreign key in the selected relationship.

Foreign Key Columns

Shows which column acts as a foreign key in the selected relationship.

Primary/Unique Key Base Table

Shows which table contains the column acting as a primary (or unique) key in the selected relationship.

Primary/Unique Key Columns

Shows which column acts as a primary (or unique) key in the selected relationship.

Identity Category

Expand to show the property fields for Name and Description.

Name

Shows the name of the relationship. When a new relationship is created, it is given a default name based on the table in the active window in Table Designer. You can change the name at any time.

Description

Describe the relationship. To write a more detailed description, click Description and then click the ellipsis (...) that appears to the right of the property field. This provides a larger area in which to write text.

Table Designer Category

Expand to show information for Check Existing Data on Creation or Re-Enabling and Enforce for Replication.

Enforce For Replication

Indicates whether to enforce the constraint when a replication agent performs an insert, update, or delete on this table. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

Enforce Foreign Key Constraint

Specify whether changes are allowed to the data of the columns in the relationship if those changes would invalidate the integrity of the foreign key relationship. Choose Yes if you do not want to allow such changes, and choose No if you do want to allow them.

INSERT and UPDATE Specification Category

Expand to show information for the Delete Rule and the Update Rule for the relationship.

Delete Rule

Specify what happens if a user tries to delete a row with data that is involved in a foreign key relationship:

  • No Action   An error message tells the user that the deletion is not allowed and the DELETE is rolled back.
  • Cascade   Deletes all rows containing data involved in the foreign key relationship. Do not specify CASCADE if the table will be included in a merge publication that uses logical records. For more information about logical records, see Grouping Changes to Related Rows with Logical Records.
  • Set Null   Sets the value to null if all foreign key columns for the table can accept null values. Applies to SQL Server 2005 only.
  • Set Default   Sets the value to the default value defined for the column if all foreign key columns for the table have defaults defined for them. Applies to SQL Server 2005 only.
Update Rule

Specify what occurs if a user tries to update a row with data that is involved in a foreign key relationship:

  • No Action   An error message tells the user that the update is not allowed and the UPDATE is rolled back.
  • Cascade   Updates all rows that contain data involved in the foreign key relationship. Do not specify CASCADE if the table will be included in a merge publication that uses logical records. For more information about logical records, see Grouping Changes to Related Rows with Logical Records.
  • Set Null   Sets the value to null if all foreign key columns for the table can accept null values. Applies to SQL Server 2005 only.
  • Set Default   Sets the value to the default value that is defined for the column if all foreign key columns for the table have defaults defined for them. Applies to SQL Server 2005 only.

Release History

14 April 2006

New content:
  • Documented the restriction on using CASCADE with logical records for merge replication.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft