Chapter 15 -- Managing Tables Using Transact-SQL and Enterprise Manager

3 4

Chapter 15

In Chapter 10, you learned how to create a table by defining its columns and data types. Once you've created a table, you can modify it in several ways, even if the table already contains data. This chapter describes some of the ways to modify a table, including altering, adding, dropping, and renaming columns and dropping an entire table. Creating and modifying table constraints (a method for ensuring data integrity) and triggers (a special type of stored procedure that is automatically executed under certain conditions) will be described in Chapters 16 and 22.

In this chapter, we will examine the use of Transact-SQL (T-SQL) and the Microsoft SQL Server 2000 Enterprise Manager in managing your tables. Keep in mind that T-SQL and Enterprise Manager do not provide the same flexibility when you are modifying a table. Enterprise Manager is more helpful because you can perform certain modifications more easily than you can by using T-SQL. Enterprise Manager displays informative error messages and sometimes suggests alternatives when you try to make an illegal modification. One benefit of using T-SQL, however, is that if you run your commands by scripting, you will have a record tracing how and in what order you made the modifications. In this chapter, we'll look at the advantages and the disadvantages of using these two techniques for modifying tables.

Before we begin, we need to create two tables in the MyDB database—Bicycle_Sales and Bicycle_Inventory—to use for the examples in this chapter. The Bicycle_Sales table contains sales information for a used bicycle dealership and includes the following columns: make_id, model_id, description, year, sale_id, price, quantity, and sale_date. The make_id and model_id columns are specified together as a foreign key constraint. This constraint references the make_id and model_id columns in the Bicycle_Inventory table, which make up the unique clustered index. As you'll see in Chapter 16, a foreign key constraint can reference only a primary key column or other unique-constraint column of the referenced table. (Constraints will be covered in detail in Chapter 16, and indexes will be explained in Chapter 17.)

The sale_id column is declared as the primary key clustered index for the Bicycle_Sales table. The CREATE TABLE statement for each of these tables is shown here:

 USE MyDB GO CREATE TABLE Bicycle_Inventory ( make_name char(10) NOT NULL, make_id tinyint NOT NULL, model_name char(12) NOT NULL, model_id tinyint NOT NULL, in_stock tinyint NOT NULL, on_order tinyint NULL, CONSTRAINT MI_clu_indx UNIQUE CLUSTERED(make_id, model_id) ) GO CREATE TABLE Bicycle_Sales ( make_id tinyint NOT NULL, --Used in foreign --key constraint model_id tinyint NOT NULL, --Also used in foreign --key constraint description char(30) NULL, year char(4) NOT NULL, sale_id int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED, price smallmoney NOT NULL, quantity tinyint NOT NULL, sale_date datetime NOT NULL, CONSTRAINT sales_inventory_fk FOREIGN KEY (make_id, model_id) REFERENCES Bicycle_Inventory(make_id, model_id) ) GO 

CAUTION


Be sure to create the Bicycle_Inventory table before you create the Bicycle_Sales table. If you attempt to create the Bicycle_Sales table first, you'll get an error message. The Bicycle_Sales table references Bicycle_Inventory via a constraint, so if the Bicycle_Inventory table is not present, the constraint cannot be created, and an error will result.

Now that we've created our sample database tables, let's make some modifications, first using T-SQL and then using 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