Creating a Table
You can use Enterprise Manager to add a table to a database. In this section, you'll add a
table to the Northwind database to store the details of a person. This table will be called
Persons, and will contain the columns shown in Table 2.8.
Table 2.8: DEFINITION FOR THE COLUMNS OF THE Persons TABLE
COLUMN NAME DATABASE TYPE LENGTH ALLOWS NULL VALUES?
PersonID int 4 No
FirstName nvarchar 15 No
LastName nvarchar 15 No
DateOfBirth datetime 8 Yes
Address nvarchar 50 Yes
EmployerID nchar 5 No
To create a table in the Northwind database, you select the Tables node of the Northwind
database in Enterprise Manager and select Action ➣ New Table. You'll then see the table
designer. Add the columns as shown in Table 2.8 to the table, as shown in Figure 2.20.
Figure 2.20: Adding a new table
N
ote The length of some of the data types is fixed. For example, the int type always uses
4 bytes of storage space, so you can't change the length of an int column from 4.
Similarly, the datetime type always uses 8 bytes of storage space. You can change
the length of nchar and nvarchar columns because those types are designed to store
variable-length data.
Click the Save button on the toolbar to save the table. In the Choose Name dialog, enter
Persons as the name, and click OK to save your table, as shown in Figure 2.21.
Figure 2.21: Entering the name of the table
N
ote Once you've saved your table, you can return to the table designer at any time by
selecting the table in the Tables node of Enterprise Manager, right-clicking the
table, and selecting Design Table.
In the rest of this chapter, you'll learn how to:
• Get additional information about the columns in a table using the Columns tab.
• Set the primary key of a table.
• Set the permissions that allow access to the contents of a table.
• Create a relationship between tables.
• Create an index to allow faster access to the information in a table.
• Create a constraint to restrict values that may be stored in a column.
The Columns Tab
In the area beneath the grid, you'll notice a tab named Columns. The Columns tab
contains additional information about the currently selected column in the grid, and
Figure 2.20, shown earlier, shows the information on the PersonID column. As you
change your selected column, the information in the Columns tab will change.
You can enter an optional description for a column in the Description field of the
Columns tab. The Default Value field allows you to supply an initial value when a new
row is added to the table; you can of course supply your own value to a column that will
override the default value.
The Precision field shows the maximum number of digits that may be used to store a
number, including those that might be stored to the right of a decimal point. The Scale
field shows the maximum number of digits to the right of a decimal point. For example,
the precision and scale of an int column are 10 and 0, meaning that an int column can
store up to 10 digits, with no digits to the right of a decimal point-no digits to the right
because an int is an integral number. The precision and scale for a money column are 19
and 4, meaning that a money column can store up to 19 digits, with up to four of those
digits to the right of a decimal point.
The Identity field allows you specify whether SQL Server should automatically assign a
value to a field. If you set the Identity field to Yes, then you can also specify values for
the Identity Seed and Identity Increment fields. You use the Identity Seed field to set the
initial value for the column, and you use the Identity Increment field to specify the
increment for value. For example, if you set the Identity Seed to 1 and the Identity
Increment to 1, then the first value for the column would be 1, the next would be 2, and
so on. The ProductID column of the Products table is an example of a column that uses
an identity to set its value.
The IsRowGuid field specifies whether a uniqueidentifier column is a globally unique
identifier known as a GUID.
Tip SQL Server doesn't automatically supply a value for a GUID. If you want SQL
Server to generate a GUID, you can use the SQL Server NEWID() function. The
N
EWID() function always returns a different value. You can then use the output from
this function as the Default Value for your uniqueidentifier column. For example,
you would set the Default Value field to [NEWID()]. You'll learn more about SQL
Server functions in the next chapter.
The Formula field allows you to set a formula that is used to assign a value to a column.
The Collation field specifies the rules that are used to sort and compare characters. You
might need to set this when working with foreign languages. For further details, consult
the SQL Server Books Online documentation.
Setting the Primary Key
Next, you'll set the primary key for the Persons table to PersonID. To do this, click on the
first row in the grid containing the PersonID column, and click the Set primary key
button on the toolbar. Once you've done this, you'll see a small key icon to the left of
PersonID.
Setting the Permissions
To set the permissions for your table, click the Show permissions button on the toolbar of
the table designer. Grant SELECT, INSERT, UPDATE, and DELETE permissions to the
public role, as shown in Figure 2.22. These permissions allow public users to retrieve,
add, modify, and remove rows from the Persons table.
Figure 2.22: Setting the permissions
Click OK to continue.
Creating the Relationship
You'll be creating a relationship between your Persons table and the Customers table. To
view the relationships screen, click the Manage Relationships button on the toolbar of the
table designer. Click New to start creating the relationship. Pick the Customers table as
the primary key table and pick the CustomerID column from this table. Make sure
Persons is selected as the foreign key table, and pick the EmployerID column from this
table. Figure 2.23 shows this. You'll notice that the relationship name is automatically set
to FK_Persons_Customers.
Figure 2.23: Creating the relationship
The check boxes at the bottom the page are as follows:
• Check existing data on creation This applies your constraint to data that might
already exist in the database when you add your relationship to the foreign key
table.
• Enforce relationship for replication Replication allows you to copy information
to a different database. When you enable Enforce relationship for replication, your
constraint is applied to the foreign key table when that table is copied to a different
database during replication.
• Enforce relationship for INSERTs and UPDATEs This applies your constraint
to rows that are added, modified, or removed from the foreign key table. It also
prevents a row in the primary key table from being deleted when there is a
matching row in your foreign key table.
• Cascade Update Related Fields This causes SQL Server to automatically update
the foreign key values of your relationship when the primary key value is
modified.
• Cascade Delete Related Fields This causes SQL Server to automatically remove
rows from the foreign key table whenever the referenced row in the primary key
table is removed. Click Close to continue.
Creating an Index
An index allows the database to quickly locate a row when you request retrieval of that
row based on a particular column value. In this section, you'll create an index on the
LastName column of your Persons table.
To view the indexes for your Persons table, click the Manage Indexes/Keys button on the
toolbar of the table designer. Click New to start creating a new index. Set the index name
as IX_LastName_ Persons, pick the LastName column, and set the order as ascending.
Figure 2.24 shows this.
Figure 2.24: Creating an index