Walkthrough: Adding and Changing a Database Diagram
New: 17 July 2006
This walkthrough illustrates how to create and modify a database diagram and make changes to the database through the database diagrams component. You will see how to add tables to diagrams, create relationships between tables, create constraints and indexes on columns, and change the level of information you see for each table.
Prerequisites
In order to complete this walkthrough, you will need:
- Access to SQL Server 2005 with the AdventureWorks sample database
- An account with database owner dbo privileges
Note
If you attempt to make changes when using an account without sufficient privileges to make changes to tables, then an error message appears.
Creating a Diagram
To create a new database diagram
On the View menu, click Object Explorer.
Open the Databases node and then open the AdventureWorks node.
Right-click the Database Diagrams node and choose New Database Diagram.
If the database does not have objects necessary to create diagrams, the following message appears: This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them? Choose Yes.
The Add Table dialog box appears.
Select AddressType (Person) and Address (Person) and click Add.
Two tables are added to the diagram.
Close the Add Table dialog box.
To view different column data
Right-click the
Address
table. On the shortcut menu, point to Table View, and then click Standard.The table grid shows three columns: Column Name, Data Type, and Allow Nulls.
Right-click the
Address
table, click Table View and select Keys.The table grid shows one column, with the table-column names. Only those columns participating in indexes appear.
Creating New Tables
To create tables within Diagram Designer
Right-click the Diagram Designer outside the existing tables and choose New Table.
In the Choose Name dialog box, click OK to accept the default name
Table1
.A new table grid appears with three columns: Column Name, Data Type, and Allow Nulls.
Add the following information to Table1:
Column Name Data Type Allow Nulls T1col1
int
checked
T1col2
varchar(50)
checked
T1col3
float
checked
Right-click
T1col1
and select Set Primary Key.A key icon will appear beside the column name.
From the File menu, click Save Diagram1.
In the Choose Name dialog box, click OK to accept the default name
Diagram1
.The Save dialog box appears with a message that
Table1
will be saved to the database. Click Yes.
Modifying Table Structure
You can add check constraints and make relationships between tables in Diagram Designer.
To create check constraints
In
Table1
, right-click theT1col3
row and choose Check Constraints.The Check Constraints dialog box appears.
Click Add.
A new constraint appears in the Selected Check Constraint list, with the default name
CK_Table1
.Select the Expression row in the grid and click the ellipsis button.
The Check Constraint Expression dialog box appears.
Type T1col3 > 5 and click OK.
Table1
now has a constraint that all values entered intoT1col3
must be greater than 5.Click Close.
To create relationships between tables
Create a new table in Diagram designer named
Table2
with the following columns:Column Name Data Type Allow Nulls T2col1
int
not checked
T2col2
varchar(50)
checked
T2col3
xml
checked
Note
The columns on the primary key side of a foreign key relationship must participate in either a Primary Key or a Unique Constraint.
Drag
T2col1
toT1col1
.Two dialog boxes appear: Foreign Key Relationship in the background and Tables and Columns in the foreground.
Click OK to save the new relationship.
Click OK again.
Creating Indexes
You can create indexes on most types of data, including XML.
To create a standard index
Right-click
Table1
and choose Indexes/Keys.The Indexes/Keys dialog box appears.
Click Add.
A new index appears in the Selected Primary/Unique Key or Index list, with a default name similar to
IX_Table1
.Select the Columns row and click the ellipsis button.
The Index Columns dialog box appears.
Click the drop-down arrow under Column Name and select
T1col2
.Note
You may add additional columns to this index by selecting the cell below
T1col2
and choosing another column name.Click OK to save this index.
Click Close in the Indexes/Keys dialog box.
To create an XML index
Right-click
T2col1
and choose Set Primary Key.Note
Adding an XML index requires that another column in the table be set as a clustered primary key.
Right-click the
T2col3
row inTable2
and select XML Indexes.The XML Indexes dialog box appears.
Click Add.
An XML index with default values will be added to the Selected XML Index list.
Click Close.
Note
XML indexes are created per-column. The first XML index is primary; any additional indexes are secondary.
Saving the Diagram
All of the changes you make to a diagram are not posted to the database until you save it. If there are problems or conflicts, a dialog box appears with more information.
To save a database diagram
On the File menu, select Save Diagram1.
The Save dialog box appears. If Warn about Tables Affected is selected, information about new or changed tables is listed.
Click OK.
If any errors occurred, the Post-Save Notifications dialog box appears with the errors and their causes. Fix the errors and save the diagram again.
Next Steps
This is a basic diagram with just two existing and two new tables, but it illustrates the potential for diagramming an existing database or creating a new schema visually. Suggestions for more exploration include:
- Create new diagrams containing groups of related tables
- Customize the amount of information shown for each table
- Change the layout and add annotations
- Copy the diagram to a bitmap
See Also
Tasks
How to: Customize the Amount of Information Displayed in Diagrams (Visual Database Tools)
How to: Set Up Database Diagram Designer (Visual Database Tools)
How to: Add Tables to Diagrams (Visual Database Tools)
How to: Define Check Constraint Expressions (Visual Database Tools)
How to: Create Relationships Between Tables on a Diagram (Visual Database Tools)
How to: Create XML Indexes (Visual Database Tools)
How to: Copy an Image of a Database Diagram to the Clipboard (Visual Database Tools)
Other Resources
Working with Diagram Layout (Visual Database Tools)
Working with Indexes (Visual Database Tools)