Managing Tables Using Object Explorer

Learn how to create tables belonging to your new database in this excerpt from the book SQL Server 2005: A Beginner's Guide.

Microsoft SQL Server 2005: The Complete Reference The following was excerpted from Chapter 2, 'SQL Server 2005 Management Studio,' of the book SQL Server 2005: A Beginner's Guide written by Dusan Petkovic and published by McGraw-Hill Osborne Media. Click here for the book excerpt series.

Bonus: TechTarget members take 45% off the price of this book at McGraw-Hill's online bookstore. Just enter promotion code AETT511 on the homepage.

Managing Tables Using Object Explorer

The next task after the creation of a database is the creation of all tables belonging to it. Again, you can create tables by using either Object Explorer or Transact-SQL.

To create a table using Object Explorer, right-click the subfolder Tables of the database, and then click New Table. The creation of a table and all other database objects using the Transact-SQL language will be discussed in detail in Chapter 4.

To demonstrate the creation of a table using Object Explorer, the department table of the sample database will be used as an example. Enter the names of all columns with their properties in the New Table dialog box. Column names, their data types, as well as the NULL property of the column, must be entered in the two-dimensional matrix, as shown in Figure 2-7.

All data types supported by SQL Server can be displayed (and one of them selected) by clicking the arrow sign in the Data Type column (the arrow appears after the cell has been selected). Subsequently, you can type entries in the Length, Precision, and Scale rows for the chosen data type in the Column Properties window (see Figure 2-7). Some data types, such as CHARACTER, require a value for the Length row, and some, such as DECIMAL, require a value in the Precision and Scale rows. On the other hand, data types such as INTEGER do not need any of these entries to be specified. (The valid entries for a specified data type are highlighted in the list of all possible column properties.)

Figure 2-7: Creating the department table using the SQL Server Management Studio

The Allow Nulls column must be checked if you want a table column to permit null values to be inserted into that column. Similarly, if there is a default value, it should be entered in the Default Value or Binding row of the Column Properties window. (A default value is a value that will be inserted in a table column when there is no explicit value entered for it.)

The column dept_no is the primary key of the department table. (For the discussion of primary keys of the sample database, see Chapter 1.) To specify a column as the primary key of a table, you must first right-click the column and then choose Set Primary Key. Finally, close the component window with the information concerning the new table. After that, the system will display the Choose Name dialog box, where you can type the table name.

To view the properties of an existing table, first double-click the folder of the database to which the table belongs. Subsequently, double-click Tables, and then right-click the name of the table and choose Properties. Figure 2-8 shows the Table Properties dialog box for the department table.

To rename a table, double-click the Tables folder and choose Rename. Also, to remove a table, double-click the Tables folder in the database to which the table belongs and select Delete.

If you create all four tables of the sample database (employee, department, project, and works_on), you can use another existing feature of SQL Server Management Studio to display the corresponding E/R- diagram of the sample database. (The process of converting the existing tables of a database in the corresponding E/R- diagram is called reverse engineering.)

To create the E/R- model of the sample database, right-click the Database Diagrams subfolder of the sample database folder and then select New Database Diagram. The first (and only) step is to select tables that will be added to the diagram. After adding all four tables of the sample database, the wizard completes the work and creates the diagram (see Figure 2-9).

Figure 2-8 The Table Properties dialog box for the department table

The diagram in Figure 2-9 is not the final diagram of the sample database, because it shows all four tables with their columns (and the corresponding primary keys), but it does not show any relationship between the tables. A relationship between two tables is based on the primary key of one table and the (possible) corresponding column(s) of the other. (For a detailed discussion of these relationships and referential integrity, see Chapter 4.)

There are exactly three relationships between the existing tables of the sample database: First, the tables department and employee have a 1:N relationship, because for each value in the primary key column of the department table (dept_no), there is one or more corresponding values in the column dept_no of the employee table employee. Analogously, there is a relationship between the tablesemployee and works_on, because only those values that exist in the primary key of the employee table (emp_no) appear also in the column emp_no of the works_on table. (The third relationship is between the tables project and works_on.)

To create each of the relationships described above, you have to redesign the diagram with the column that corresponds to the primary key column of the other table. (Such a column is called a foreign key.) To show this, you can use the employee table and define its column dept_no as the foreign key of the department table.

Figure 2-9: First diagram of the sample database

Click the created diagram (it is called sample_diagram in our example), right-click the graphical form of the employee table in the detail pane, and select Relationships. In the Foreign Key Relationships dialog box, select Add.

Expand Tables and Columns Specification and click . . . In the Tables and Columns dialog box, select the table with the corresponding primary key (the department table). Choose the dept_name column of this table as the primary key and the column with the same name in the employee table as the foreign key and click OK.

Figure 2-10 shows the modified sample_diagram diagram after all three relationships in the sample database have been created.

Figure 2-10: The final diagram of the sample database

Click for the complete book excerpt series.

Dig Deeper on SQL Server Database Modeling and Design