Creating Tables and Relationships

The primary entities in any database are its tables. Tables are composed of a structure and data. Server Explorer is the Visual Studio instrument used to define or edit the structure or data of any table within a connected database. In fact, using Server Explorer, it is possible to create a new SQL Server database instance from scratch.

Note

As we noted in Chapter 5, the Express and Standard editions of Visual Studio refer to the Server Explorer as the Database Explorer. For simplicity, we will always refer to this window as the Server Explorer in this chapter.

Creating a New SQL Server Database

Data connections are physical connections to a database. In Server Explorer, the Data Connections node has a list of every established database connection. To start the database creation process, right-click on the Data Connections node and select the Create New SQL Server Database option. In the resulting dialog box (see Figure 15.1), you will need to provide a server name, login credentials, and a name for the new database.

Figure 15.1. Creating a new SQL Server database.

This will immediately create the indicated database and add a connection to the new database under the Data Connections node. Figure 15.2 shows the newly created Contoso database added to the list of connections.

Figure 15.2. The new database added to the data connections.

Adding an Existing Database

Of course, you can also establish a connection to an existing database. Again, you right-click the Data Connections node; this time, though, you select the Add Connection option. The Add Connection dialog box (see Figure 15.3) is similar to the new database dialog box: You specify a data source, server name, login credentials, and a database name/database filename to connect to the database.

Figure 15.3. Connecting to an existing database.

Under each connection are folders for the following classes of database objects:

• Database Diagrams

• Tables

• Views

• Stored Procedures

• Functions

• Synonyms

• Types

• Assemblies

These folders are the launching point for creating corresponding objects within the database.

Defining Tables

The Table Designer is the Visual Studio tool you use to define or edit the definition for a table. Using the Server Explorer window, right-click the Tables folder under an existing connection and select Add New Table. The Table Designer will open in the main document pane of the IDE.

The designer is implemented in a tabular format; you add a row in the designer for every column you want to define in the table. For each table column, you specify a name, data type, and nullability. In addition to the tabular designer interface, a Properties window is also present that provides complete access to all of the different properties for any given column in a table (see Figure 15.4).

Figure 15.4. Defining a table's columns.

In addition to the basics, the Table Designer also allows you to define a column, or group of columns, as part of the primary key for the table, or as part of an index.

Setting a Primary Key

With the Table Designer active in the IDE, a new Table Designer top-level menu item is available. You can use this menu, or the shortcut menu displayed whenever you right-click within the Table Designer, to access a list of useful actions. For instance, to create a primary key for the table, you would select the column or columns that constitute the key and then select Set Primary Key from the designer's menu. A key icon will indicate any primary keys defined in the table.

Creating Indexes, Foreign Keys, and Check Constraints

Indexes, foreign keys, and check constraints are all created using the same interface and process: Select the appropriate action from the Table Designer menu; use the settings dialog box to first add the index, key, or constraint; and then set its properties in the property grid. As an example, to create an index across one or more columns in the table, select the Indexes/Keys item from the Table Designer menu. In the Indexes/Keys dialog box (see Figure 15.5), you can add a new index and then set its properties in the property grid.

Figure 15.5. Creating an index.

Column population for the index is controlled with the index's Columns property; a separate Index Columns dialog box (see Figure 15.6) enables you to change the column membership and specify the sort order for each column.

Figure 15.6. Column membership in an index.

Using the Database Diagram Designer

The aforementioned Table Designer and dialog boxes allow you to define tables and table-related constructs on a table-by-table basis. The Database Diagram Designer provides the same functionality in a more visual format. It allows you to build a diagram of the whole database showing tables, table columns, keys, and table relationships, and also allows you to create each of these items from within the Diagram Designer tool.

Like the Table Designer, the Database Diagram Designer is implemented within the IDE's document pane. It has its own menu and toolbar associated with it; many of the commands on the menu/toolbar can be accessed through the designer's shortcut menu by right-clicking anywhere within the designer.

Tip

Within a diagram, you can change the view style on a per-table basis. Right-click the table and select one of the available Table views: Standard (shows column name, data type, and allow nulls), Column Names, Keys, Name Only, and Custom (you select the data you want to display). The Name Only view is particularly useful if you want to see an entire database diagram to get a sense of the relationships without necessarily caring about the table details themselves.

Creating a Database Diagram

To create a database diagram, right-click on the Database Diagrams node in the Server Explorer window and select Add New Diagram. A blank diagram will open, and the designer will immediately display a dialog box for adding tables to the diagram (see Figure 15.7).

Figure 15.7. Adding tables to a diagram.

After you've added a few tables, the diagram shows a graphical representation of the tables' columns and any relationships that the tables participate in. The diagram is fully interactive; you can directly edit column definitions, keys, relationships, and so on.

Tip

Here is a quick shortcut for adding groups of related tables: Add a table to the diagram, select it, and click on the Add Related Tables button in the designer's toolbar. This will automatically add to the diagram any table in the database that has a current relationship with the selected table.

Modifying Table Definitions

Tables can be edited "in-line" within the diagram. To change column details, you click within the table and then enter column name information or change the data type and nullability rules. To add a column, just fill out a new row within the table representation in the diagram.

Building Table Relationships

Table relationships are easy to define within a diagram: Just drag and drop the primary key column from one table to the foreign key column on another table. This will automatically kick off two dialog boxes: Foreign Key Relationships and Tables and Columns (these are the same dialog boxes used to create foreign keys in the Table Designer). Figure 15.8 captures the foreign key and primary key assignments for the creation of a common one-to-many relationship between a category table and an order table. The order table has a category ID column (category_id) that will be foreign-keyed to the primary key on the category table (id).

Figure 15.8. Creating a foreign key.

After committing the column assignments, you complete the relationship by changing any properties (if needed) on the relationship itself in the Foreign Key Relationships dialog box (see Figure 15.9).

Figure 15.9. Creating a foreign key.

Relationships are depicted within the diagram as a line between the two tables. The line indicates the direction of the relationship by showing a key on the primary key side and an infinity symbol on the foreign key side (or the "many" side) of the relationship. Figure 15.10 illustrates the order category table to order table association as it would appear within the Database Diagram Designer.

Figure 15.10. Two tables related in the Database Diagram Designer.

Note

By default, relationships will enforce referential integrity. That is, they will prevent any action (insert, update, delete) that would result in a mismatch of keys between the two related tables. This would include inserting a foreign key (FK) value when it doesn't exist as a primary key (PK) in the related table, changing a PK value that is referenced as a FK value, and so on.

You can control whether a relationship enforces referential integrity through the Enforce Foreign Key Constraint setting in the Foreign Key Relationship dialog box. Relationships that do not enforce referential integrity are depicted as banded lines instead of solid lines within the Diagram Designer. You should also note that the Diagram Designer will show only relationships that have been explicitly defined through the process we cover in the preceding paragraphs. Just having similarly named foreign keys and primary keys will not automatically create a relationship for you.

In addition to one-to-many relationships, you can also model one-to-one, many-to-many, and reflexive relationships using the Database Diagram Designer.

One-to-One Relationships

You build a one-to-one relationship in the same way you create a one-to-many relationship. The difference is this: One-to-one relationships are between two primary keys instead of a primary and a foreign key. If you drag a primary key column from one table to a primary key column on another table, this will automatically create a one-to-one association. These relationships are depicted with a key icon on both ends of the relationship line.

Many-to-Many Relationships

You create a many-to-many relationship with the help of a junction table. If you had to model a many-to-many association between an order table and an item table (an order can have many items, and an item can belong to many orders), you would first add a third table to the database to hold the foreign keys of this relationship.

After adding the junction table, you would then establish a one-to-many relationship between the order and orderitem table and the item and orderitem table. The last step is to define the multicolumn primary key on the junction table. Figure 15.11 shows the results in the diagram.

Figure 15.11. A many-to-many relationship.

Reflexive Relationships

A reflexive relationship is a relationship between a table and itself. A typical example used to illustrate reflexive relationship is that of a part table that relates back to itself to represent the fact that a part could be made up of other parts. In this case, the part table might carry a parent_part_id field that is meant to be a foreign key related to the employee table's primary key.

To create a reflexive relationship, select the primary key column and drag it back onto the same table. The configuration of the key associations and the relationship values is the same as with any other relationship. Figure 15.12 shows a diagram of a reflexive relationship.

Figure 15.12. A reflexive relationship.