Creating a Table Using Enterprise Manager

3 4

Now that you understand the basic elements of creating tables using SQL Server, let's step through an example of using Enterprise Manager to create a table. Before you begin this process, however, it is critical to remember that when you're designing your own databases, you should design all your database tables and their relationships before you begin to actually create them. To create a database table by using Enterprise Manager, follow these steps:

  1. In Enterprise Manager, expand a SQL Server group and then expand a server.
  2. Expand the Databases folder to view the available databases.
  3. Expand the database in which you want to work—in this case, MyDB.
  4. Right-click the Tables folder and then choose New Table from the shortcut menu that appears. The New Table window appears, as shown (maximized) in Figure 10-7.

    click to view at full size.

    Figure 10-7. The New Table window.

    The New Table window contains a spreadsheet-like grid. Each row in the grid represents a column in your table. Each column in the grid represents a different attribute of the table column—data type, length, or nullability.

    NOTE


    You should adopt standards when naming your table columns. It really doesn't matter what type of naming convention you adopt, but be consistent. Every time you use the same column in different tables, use exactly the same name. This consistency will help avoid confusion when you are performing queries.

  5. Define each of the columns in your database table—working in one row at a time—by typing the name in the Column Name column, choosing the data type from the pull-down menu in the Data Type column, choosing the length where applicable (such as for character data types), and pressing the Shift key in or clicking in the Allow Nulls column to toggle the check mark (to allow or disallow null values, respectively). The Product_Info table is shown in Figure 10-8. Notice that we chose our user-defined data type brand_type for the Brand_ID column. But also notice that, by default, the check mark in the Allow Nulls column is turned on, even for our brand_type data type, which was created not to allow null values. You should turn off this check mark here to be consistent with the data type's intended nullability.

    The data in your table rows will be physically stored in the order in which you have defined your columns. If you want to insert a column definition row in this grid between two existing rows, right-click the row just below where you want to insert a new row and choose Insert Column from the shortcut menu that appears. To delete a row, right-click the appropriate row and choose Delete Column from the shortcut menu. In the Product_Info table example, we'll set the Product_ID column as the primary key column by right-clicking the Product_ID column name and choosing Set Primary Key from the shortcut menu. A key icon will appear next to that column name, as shown in Figure 10-9. (Primary keys and other constraints are discussed in Chapter 16.)

    click to view at full size.

    Figure 10-8. Defining columns in the New Table window.

  6. At the bottom of the window is a tab labeled "Columns." This tab allows you to change some attributes of the column that is selected at the top. For example, we selected the Brand_ID column and then assigned a description and a default value of 0 for it in the Columns tab below, as shown in Figure 10-10.

    click to view at full size.

    Figure 10-9. The Primary Key indicator.

    click to view at full size.

    Figure 10-10. The Columns tab.

  7. You can create other constraints and indexes on the table by right-clicking any column name and choosing Indexes/Keys, Relationships, Constraints, or Properties from the shortcut menu, or by clicking the Table And Index Properties icon next to the Save icon on the toolbar. Any of these methods will get you to the Properties window, shown in Figure 10-11. Your table name will appear as Table1, Table2, or something similar. Ours is named Table2. You can change this name, as shown in the next step, when you save your table. There are four tabs in this window. These will be explained in more detail throughout the book.

    Figure 10-11. The Properties window for tables and indexes.

  8. To name this new table, click the Save icon and a dialog box will appear where you can type the table name. Type the name you want and click OK, and the table you have designed will be created and its information saved. You can close the New Table window now, and you will see your table name appear in the right-hand pane of Enterprise Manager.


Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net