Part II: Administering a SQL Server
Chapter 8: Creating Databases, Tables, and Relationships
Creating Databases, Tables,
and Relationships
In This Chapter
䊳Setting up your database
䊳Using SQL Server Management Studio Express
䊳Defining and building tables
䊳Enforcing data constraints
䊳Identifying relationships among tables
W
hen you want to store information in SQL Server 2005 Express, your first responsibility is to define a database that will serve as a container for your records. Your next job is to create the tables where the actual data will reside, along with any restrictions on what you can place in these tables. Finally, by defining relationships among your information, you help SQL Server 2005 Express ensure good data integrity and protect your business rules. In this chapter, you see how to accomplish all these tasks. To begin, you get the hang of using the handy (and free) SQL Server Management Studio Express tool, which lets you do all the database management tasks I describe through- out the chapter. Because you may have other preferences when working with a data management tool, I also show you some different approaches you can take to achieve the same results.Using SQL Server Management
Studio Express
SQL Server Management Studio Express packs a lot of features into a small (and free!) package. You can use it to
⻬View the structure of your SQL Server 2005 Express instance, including databases, tables, views, stored procedures, and so on.
⻬Connect to other SQL Server instances.
⻬Perform many administrative tasks.
⻬Run queries and other data access jobs.
⻬View results, or export them to text files.
Not bad for a free tool! You can download it from the Microsoft Web site (www.microsoft.com/sql/editions/express/default.mspx); instal- lation is a snap. Here’s a brief tour through the product, highlighting those features that you’re most likely to use right away.
First, launch SQL Server Management Studio Express by choosing Start➪
All Programs➪Microsoft SQL Server 2005➪SQL Server Management Studio Express. You’re prompted with a dialog box that lets you specify which server you want to connect, as well as your login details.
Depending on how you’ve configured your environment, you’ll notice that the user interface is divided among several components. One of the most impor- tant of these entities is the Object Explorer, which I describe next.
The Object Explorer
The Object Explorer in the SQL Server Management Studio Express user interface is responsible for listing, in tree format, the following vital bits of information:
⻬The connection: This portion of the tree tells you about the connection, including the names of the computer and SQL Server 2005 Express instances.
⻬Database details: If you expand the Databases folder, you’re treated to a collection of really important system folders. These include a list of system databases, as well as an inventory of your own databases.
108
Part III: Adding and Accessing a SQL Server 2005 Express Database
Expand any of your database folders and you see details broken out as follows:
•Database Diagrams: Here’s where you can get a graphical view of your information, including the relationships you’ve defined among objects in your database.
•Tables: The contents of this folder are divided between system tables, which are provided for and looked after by SQL Server 2005 Express, and user tables. Each table further breaks down into columns and indexes.
•Views: Views are virtual tables, composed of information from one or more “real” tables. If you expand this folder, you see a list of both system and user-defined views. Opening a particular view yields a list of the columns that make up the view.
•Synonyms: These are substitute names for objects in your database.
•Programmability: Here’s where you can get a list of all your system and user-defined stored procedures, including their input and output parameters. You can also find out about your functions via this tree entry.
•Security: This folder itemizes all the users who have access to your database.
⻬Security: The Security folder contains all the login names for people who are authorized to use your database server, along with the server roles and credentials you’ve set up. Note that this folder is different than the Security folder within a database. The latter simply lists all those already authorized database server users who also have access to that database.
⻬Server Objects: This folder holds details about those devices that you’ve defined for backup purposes, as well as any servers linked to this database server, and any triggers that have been defined on the server.
⻬Replication: SQL Server 2005 Express offers the ability to subscribe to data feeds from other SQL Server instances. This folder cites any active subscriptions.
⻬Management: You can view a collection of your SQL Server logs, includ- ing current and archived versions. These are excellent sources of diag- nostic information about your database server.
Figure 8-1 is an example of the SQL Server Management Studio Express Object Explorer, with many of its folders expanded.
109
Chapter 8: Creating Databases, Tables, and Relationships
To get the latest-and-greatest view of everything about your SQL Server 2005 Express instance, right-click anywhere on the Object Explorer tree and choose Refresh.
The Query window
The Query window is where you and SQL Server 2005 Express get to know each other, up close and personal. The workspace contains input windows (described as the Query Editor) for queries and other data interaction. You can also elect to have your output show up at the bottom of the screen in the area (described as the Output window). You can create as many Query Editor windows as you need simply by clicking the New Query icon on the Standard toolbar.
When you’re comfortable working in the workspace, you can decide how and where you want your results to appear. You have three options; click one of these icons at the top of the window:
Figure 8-1: The SQL Server Management Studio Express Object Explorer.
110
Part III: Adding and Accessing a SQL Server 2005 Express Database
⻬Text: This formats your output into simple text, and places it in the Output window.
⻬Grid: This option provides more attractive results, placing them into a grid at the bottom of the Output window.
⻬File: This is the “to-go” option for your queries. SQL Server 2005 Express takes the results and nicely places them in a text file.
Creating Key Database Objects
In this section, I show you how to use a combination of graphical and text tools to create and maintain major database objects.
Databases
Creating, altering, and deleting databases are significant events in the life of your SQL Server 2005 Express environment. However, none of these tasks requires much heavy lifting, as you’ll now see.
Creating a database
To create a database, just follow these steps:
1. Launch SQL Server Management Studio Express.
2. Connect to the appropriate SQL Server 2005 Express instance. 3. Expand the connection’s entry in the Object Explorer.
4. Highlight the Databases folder.
5. Right-click this folder, and choose New Database.
The New Database dialog box appears (as shown in Figure 8-2) that lets you specify the new database’s name, as well as a collection of proper- ties about the new database.
6. Enter values for these prompts (as shown in Figure 8-3), and click OK.
In many cases, you can safely accept the default values from the Options page.
111
Chapter 8: Creating Databases, Tables, and Relationships
Figure 8-3: Setting database properties in SQL Server Management Studio Express. Figure 8-2: Creating a database in SQL Server Management Studio Express.
112
Part III: Adding and Accessing a SQL Server 2005 Express Database
Renaming a database
After you have a database in place, renaming it is easy:
1. Launch SQL Server Management Studio Express.
2. Connect to the appropriate SQL Server 2005 Express instance. 3. Expand the connection’s entry in the Object Explorer.
4. Expand the Databases folder.
5. Right-click the database whose name you want to change, and choose the Rename option.
6. Enter a new name of your choice for the database, and press Enter to save your modification.
Deleting a database
When the time comes to say goodbye to a database, all you need to do is follow these steps:
1. Launch SQL Server Management Studio Express.
2. Connect to the appropriate SQL Server 2005 Express instance. 3. Expand the connection’s entry in the Object Explorer.
4. Expand the Databases folder.
5. Right-click the database that you want to remove, and choose the Delete option.
6. Click OK in the confirmation dialog box.
Renaming and dropping databases can be hazardous to your applications’ health. Creating a backup is a good idea before making changes of this magni- tude. The sanity you save may be your own. Check out Chapter 13 for more about archiving your information.
Tables
Tables are where the rubber meets the road in a database: they dutifully store your vital information. I show you how to create and maintain these important structures.
Creating a table
After you decide that you want to create a new table, bringing it into exis- tence is easy. You have two major routes that you can follow: SQL/application
113
Chapter 8: Creating Databases, Tables, and Relationships
code versus a graphical tool. In the following list, I describe the advantages and drawbacks of each:
⻬SQL/application code:Choosing this route means that you directly enter, via either SQL or an application programming language, the exact syntax that you want to create your table. This approach has some very compelling advantages:
•Power and flexibility:You can specify exactly what you want to happen; a graphical tool may not be able to specify all the nuances that you can in SQL.
•Repeatability:You can group your table creation statements into a script or program that you can run again and again. This is much less tedious than having to manually create the table in a graphical tool. •Automation:If you’re shipping a self-installing application to exter-
nal locations or need other automation features, creating your tables with SQL or application code is the only way to go. You definitely don’t want to burden your users with the responsibility of using a graphical tool to manually create your tables.
The main drawback to this approach is that it does require more under- standing of SQL or the programming language of your choice.
⻬Graphical tool:This is the flip side of SQL. Using a graphical tool might just mean that you can escape all the vagaries of the CREATE TABLE
statement. This is a laudable goal, so if your table designs are simple, and you don’t want to learn any SQL, use the graphical tool of your choice to create your table.
Many graphical tools on the market can create SQL Server 2005 Express tables. Figure 8-4 is an example of SQL Server Management Studio Express creating a table.
Figure 8-4: Creating a table with SQL Server Management Studio Express.
114
Part III: Adding and Accessing a SQL Server 2005 Express Database
If you change your mind, and suddenly become very curious about the
CREATE TABLEstatement, many of these graphical tools can generate the SQL for you, as well as show you what SQL they used to create your table. Here’s how to build tables, using SQL or another programming language:
1. Open SQL Server Management Studio Express. 2. Open the Databases folder.
3. Expand the folder for your database. 4. Click the New Query button.
5. Type your SQL.
Here’s a snippet of SQL that creates a basic table:
CREATE TABLE partner (
partner_id INTEGER PRIMARY KEY, partner_name VARCHAR(30),
partner_area VARCHAR(10) );
6. After you’re ready to create the table, click the Execute button. 7. Check the results to make sure things ran correctly.
If they did, you receive a message like this:
Command(s) completed successfully.
8. If things ran successfully, save the code by clicking the Save button.
Make sure that you’re in the Query window and not the results window before clicking the Save button.
You now have a table that’s ready to be filled with important data, plus the actual code that built the table. How can you run this code again? You can run the code in at least two ways. First, you can simply open the file in SQL Server Management Studio Express, and click the Execute button to launch the SQL. Alternatively, you can use the SQLCMD tool to run the script from the command line.
SQLCMD is a very helpful utility that allows both batch and interactive access to SQL Server 2005 Express. Follow these steps to use SQLCMD:
1. Open a command prompt.
Choose Start➪Run and enter cmd. Or choose Programs➪Accessories➪
Command Prompt. When you see the friendly command prompt, it’s time to launch SQLCMD.
115
Chapter 8: Creating Databases, Tables, and Relationships
2. Run SQLCMD, passing the proper parameters.
This can get a bit confusing: SQLCMD is rather picky about the exact syntax that it deigns to run. This is not surprising when you realize that it supports over two dozen parameters. Table 8-1 highlights a small group of key parameters.
If you get in hot water, you can always ask SQLCMD for help:
SQLCMD /? 3. Run your script.
Here’s an example of how I ran SQLCMD, along with the parameters I provided:
SQLCMD -S dbserver -U Nicole -P Sierra -d WestBay -i build_abc.sql
Make sure that your script file is in the right directory; SQLCMD can’t find it otherwise. Alternatively, provide a full path to the file.
Table 8-1
Key SQLCMD Parameters
Parameter Purpose
-S Specify the server that you want to connect to
-U Provide your username
-P Provide your password
-d Which database to use
-i The file containing your SQL script
Modifying a table
If you made a mistake when you created a table, don’t despair! Modifying the table to your liking is no big deal. You can choose from several approaches when amending an existing table. The right approach is largely dependent on what kind of modification you’re making.
If you’re only renaming a table, here’s the simplest way to make it happen:
1. Open SQL Server Management Studio Express. 2. Open the Databases folder.
3. Expand the folder for your database.
116
Part III: Adding and Accessing a SQL Server 2005 Express Database
4. Expand the Tables folder.
5. Position the cursor on the table you want to rename, and click once. 6. Rename the table as you like, and press Enter.
That’s all renaming takes! Now, if you want to make more complex changes, you’ll probably need to use straight SQL or a more robust graphical tool. This is true for any of these kinds of adjustments:
⻬Adding a column
⻬Changing a column’s data type
⻬Removing a column
⻬Changing default values, constraints, and so on
This is just a partial list; several other types of alterations require a visit to SQL land. Luckily, the SQL for these kinds of modifications is quite easy. Here’s how to proceed:
1. Back up your table.
Mistakes happen to the best of us. To help recover from the slight possi- bility of catastrophic error, creating a backup is always a good idea before undertaking any kind of major database change.
2. Launch SQL Server Management Studio Express, and click on the New Query button.
Using SQL Server Management Studio Express makes the most sense; it’s free, and provides great functionality. You can see your table’s structure, which helps reduce the chance of any inadvertent SQL errors.
3. Type your SQL statement, or make the changes graphically.
I describe the SQL approach; however, when given the choice, you can opt for the graphical approach.
The ALTERstatement is very flexible, and is generally the right choice for making table modifications. Of course, you need to make sure that you have permission to use this statement. After you have permission, here are some examples of ALTERin action. First, here’s the original
CREATE TABLEstatement:
CREATE TABLE partner (
partner_id INTEGER PRIMARY KEY, partner_name VARCHAR(30),
partner_area VARCHAR(10) );
117
Chapter 8: Creating Databases, Tables, and Relationships
•Dropping a column: When you realize a certain column is no longer necessary, you can easily remove it:
ALTER TABLE partner DROP COLUMN partner_area;
•Adding a new column: After dropping a column , you may want to bring it back. Fortunately, you only need to run the ALTERstate- ment to re-create the column:
ALTER TABLE partner
ADD partner_area VARCHAR(10)
•Creating a default value constraint: SQL Server 2005 Express lets you set up restrictions on your tables. These are known as con- straints, and I discuss them a little later in this chapter. For now, here’s a simple default value constraint for the partner_area
column:
ALTER TABLE partner
ADD CONSTRAINT partner_area_unassigned DEFAULT ‘unassigned’ FOR partner_area ; 4. Run your statement.
5. Check for any problems.
If things work out okay, you receive a message like this:
Command(s) completed successfully.
If you receive an error message, try modifying your SQL statement to correct the problem.
Removing a table
Getting rid of tables you no longer need is very easy. Just follow these simple steps.
1. Back up your data.
Unless you’re sure that you’ll never need to set eyes on this data again, consider making a backup. Even if you don’t need the data, you might need to re-create the table’s structure at some point later.
2. Choose the method you want to use.
You have numerous tools at your disposal when you create a table. The same holds true when dropping a table. Of all your choices, however, SQL Server Management Studio Express is the safest: Because you can see all your tables, you more than likely won’t delete the wrong table.
118
Part III: Adding and Accessing a SQL Server 2005 Express Database
3. Drop the table.
The SQL syntax for deleting a table is very simple:
DROP TABLE SoonToBeGone;
You don’t need to first delete any of the table’s data or remove any of its indexes or relationships: The DROPstatement takes care of all cleanup.