Preserve referential integrity in Database Model diagrams
What to you want to do?
Understand referential integrity rules
Referential integrity rules keep database users from accidentally breaking the mapping between related columns.
Let's say you have a database called Books with two tables named Titles and Authors. For purposes of this example, each title has only one author, but each author can have multiple titles, creating what is called a one-to-many relationship. The table on the "one" side of the relationship is called the parent table. The table on the "many" side is called the child table.
In order to gather together information about each book and its author, a query uses keys to match each book in the Titles table to its author in the Authors table. The parent table (Authors) must have a primary key that uniquely identifies each row. The Titles table must have a column that holds the AuthorID for each title. This column is called the foreign key. The primary key identifies each author, and the foreign key identifies which books were written by which author.
If a user changes the author's AuthorID in the parent table, without updating the AuthorID in the Titles table, the books in the Titles table no longer have a valid author.
Instead of having to change the AuthorID in both tables, you can set referential integrity rules to take the action you want. You can choose to have the database model take specific actions when a key is changed or deleted in the parent table.
Note: The terms parent and child are used to mean something quite different in categories, which are created with the Parent to category, Category, and Category to child shapes. For more information on categories see Define categories in a Database Model diagram.
Set actions for referential integrity
-
Double-click the relationship for which you want to set a referential action.
-
In the Database Properties window, under Categories, click Referential Action.
-
Click the action that you want the database to take on the corresponding value in the child table. You can choose from the following actions:
-
No action When a value in a parent table's primary key is changed or deleted, the corresponding value in the child table's foreign key is left unchanged.
-
Cascade When a value in a parent table's primary key is changed or deleted, the corresponding value in the child table's foreign key is changed or deleted to match the primary key.
-
Set NULL When a value in a parent table's primary key is changed or deleted, the corresponding value in the child table's foreign key is set to NULL.
-
Set default When a value in a parent table's primary key is changed or deleted, the corresponding value in the child table's foreign key is set to a default that you choose.
-
Do not enforce This action turns off referential integrity rules for this relationship.
-
Note: If the Set NULL and Set Default options are disabled, it is likely that the relationship is set to Optional on the Miscellaneous tab of the Database Properties window for the relationship.
Set a default value for a column
To use the Set default action, you must provide a default value for the column.
-
Double-click the table in the diagram to open the Database Properties window.
-
Under Categories, click Columns, click the column that you want to create a default for, and then click Edit.
-
On the Definition tab of the Column Properties dialog box, enter a value for the default.
Show referential integrity using notation
You can set your Document Options to hide or show referential integrity notation next to the relationship lines in your model.
What do the letters in referential integrity notation mean?
Referential integrity notation shows relationships with two letters separated by a colon, in the form [action taken in the parent table]:[result in the child table]. For example, d:C means that when something is deleted in the parent table, the change is cascaded to the child table.
Actions in the parent table are indicated by a lowercase letter:
-
u Data in the parent table is updated.
-
d Data in the parent table is deleted.
Results in the child table are indicated by an uppercase letter:
-
R No action Don't change the child table.
-
C Cascade Replicate the change in the child table.
-
D Set default Insert the default value you previously set for that column in the child table.
-
N Set Null Set the child table to NULL.
-
(Blank) Do not enforce referential integrity on this relationship.
Note: These settings affect what happens in an actual database rather than in a model. In a model that you reverse engineer from an existing database, you can see how the database is designed with regard to referential integrity. In a model you create from scratch, you can use these settings to document how you want the database you are modeling to behave.
Show referential integrity
-
On the Database menu, point to Options, and then click Document.
-
On the Relationships tab, under Show, select the Referential integrity check box.
No comments:
Post a Comment