MASTERING SQL SERVER 2000- P10

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
31
lượt xem
6
download

MASTERING SQL SERVER 2000- P10

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p10', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P10

  1. 420 CHAPTER 11 • TABLES 4. To see the new record, choose Query ➣ New Query. 5. Enter and execute the following code: SELECT * FROM customers 6. Notice that the record now exists with a custid of 1 (that is because of the iden- tity property discussed earlier, which automatically added the number for you). 7. To test the check constraint by adding characters in the zip field, choose Query ➣ New Query. 8. In the query window, enter the following code and note the letters in the zip code field: USE sales INSERT customers VALUES (‘John’,’Smith’,’817 3rd’,’Chicago’,’IL’,’AAB1C’,’8015551212’) 9. Notice in the results pane that the query violated a constraint and so failed.
  2. RESTRICTING THE DATA 421 Another tool at your disposal for protecting against incorrect data is the rule. Rules work just like constraints, validating user data before it is allowed in the table. The only difference between rules and constraints is that rules can be bound to a user- defined datatype, and constraints cannot. Binding a rule will attach the rule to the datatype so that everywhere you use that datatype, the rule is already in place, whereas a constraint would need to be specifically applied to the column every time you used it. Let’s generate a rule for your state datatype so you can see how it’s done: 1. Open Enterprise Manager and expand your server, then databases, then Sales. 2. Under Sales, select Rules. PA R T 3. From the Action menu, select New Rule. III 4. To create a rule that will accept only 5 of the 50 state abbreviations, type State in the Name box and enter the following in the Text box (feel free to add your own state here if you like): @state in (‘AZ’,’CA’,’WY’,’NY’,’FL’) Digging into SQL Server
  3. 422 CHAPTER 11 • TABLES 5. Click OK to create the rule. 6. Once back at Enterprise Manager, double-click the state rule to open its properties. 7. Click the Bind UDTs button to bind the new rule to the state datatype. 8. Check the Bind box next to State to bind the rule, and click OK. Note that you can also bind this to a column in a table, just like a constraint.
  4. RESTRICTING THE DATA 423 Now that the state rule is bound to the state datatype, every time you use the state datatype, it will have the rule in place already. In your case, every time you use the state datatype on a column, it will allow only one of the five states in the list you cre- ated for the rule. It is easy to see how the check constraint can be a powerful ally against entering wrong data—all you need to do is figure out what data belongs in your column and create a constraint instructing SQL Server not to accept anything else. Check con- straints serve no purpose if your users simply forget to enter data in a column alto- gether, though—that is what default constraints are for. Using Default Constraints Default constraints are used to fill in fields that the users leave blank by not including them in the INSERT or UPDATE statement that they used to add or modify a record. There are two types of defaults: object and definition. Object defaults are defined when you create your table and affect only the column on which they are defined. Defini- tion defaults are created separately from tables and are designed to be bound to a user- defined datatype (just like the rule we discussed earlier). Either type of default can be a big time-saver in a data entry department if you use the default right. For example, suppose that most of your clientele live in California and that your data entry people must type CA for every new customer they enter. That may not seem like much work, but if you have a sizable customer base, that can add up to a lot of typing. By using a default constraint, however, your users can leave the state field intentionally blank, and SQL Server will fill it in for you. To demonstrate the capabili- ties of the default constraint, let’s create a definition default on the customers table: 1. Open Enterprise Manager and expand your server, then databases, then the Sales database. 2. Click the Tables icon under Databases. 3. Right-click the customers table and select Design Table. PA R T 4. Click State. In the bottom half of the screen, in the Default Value column, III type 'CA' (with the single quotes). Note that SQL Server will place this inside parentheses. Digging into SQL Server
  5. 424 CHAPTER 11 • TABLES 5. Click the Save button and exit the table designer screen. 6. To test the default, open Query Analyzer by selecting it from the Tools menu in Enterprise Manager. 7. Enter and execute the following code: USE sales INSERT customers (fname, lname, address, city, zip, phone) VALUES (‘Tom’,’Smith’,’609 Georgia’,’Fresno’,’33405’,’5105551212’) 8. To verify that CA was entered into the state field, select New Query from the Query menu. 9. Enter and execute the following code: SELECT * FROM customers 10. Notice that the Tom Smith record has CA in the state field, as shown in the graphic below.
  6. RESTRICTING THE DATA 425 Definition defaults are great for affecting just a single column like you did here, but because state is a user-defined datatype that can be used in any of your tables in the Sales database, it would make more sense to have the default bound to the datatype so that you don’t need to rewrite it every time you use the datatype. That is what object defaults are for—binding to a datatype. Let’s create an object default that will fill in the state field with CA if the user forgets: 1. Open Enterprise Manager and expand your server, then databases, then the Sales database. 2. Under Sales, select Defaults. From the Action menu, select New Default. 3. In the Name field, type StateOD. 4. In the Value field, enter 'CA' (with the single quotes). PA R T III Digging into SQL Server
  7. 426 CHAPTER 11 • TABLES 5. Click OK to create the default. 6. Once back at Enterprise Manager, double-click the new default to bring up its properties. Click the Bind UDTs button to bind the default to a user-defined datatype. 7. Check the Bind box next to State to bind the default to the state datatype. Now that the StateOD default is bound to the state datatype, every time you create a field with the state datatype, the field will have a default in place that will automati- cally fill the field with a value of CA if the user doesn’t enter a value. That is all there is to enforcing domain integrity—controlling what your users can enter into your fields. You can use check constraints to force your users to enter the proper data, and default constraints will fill in any data that your users might forget. However, there are still two more types of integrity to enforce. Next we will see how to keep users from entering duplicate records by enforcing entity integrity. Enforcing Entity Integrity Ensuring that each of the records in your tables is unique in some way and that no record is accidentally duplicated is referred to as enforcing entity integrity. Why do you need to be sure that there are no duplicate records in your tables? Imagine what would happen if a customer were accidentally entered twice in your customers table, thus duplicating the data. You would have one customer with two different IDs, mak- ing it very difficult to decide which one to bill for orders. Or, worse yet, suppose that someone had accidentally entered two customers with the same ID. This could cause big problems when making sales or generating reports, because you would not know which customer actually bought what—they would both show up as the same customer.
  8. RESTRICTING THE DATA 427 Such a mess as this can be avoided by enforcing entity integrity. There are two ways to enforce entity integrity—the first is with a primary key. Using Primary Keys A primary key is used to ensure that each of the records in your table is unique in some way. It does this by creating a special type of index called a unique index. An index is ordinarily used to speed up access to data by reading all of the values in a column and keeping an organized list of where the record that contains that value is located in the table. A unique index not only generates that list, but it does not allow duplicate val- ues to be stored in the index. If a user tries to enter a duplicate value in the indexed field, the unique index will return an error, and the data modification will fail. Suppose, for instance, that you have defined the custid field in the customers table as a primary key and that you have a customer with id 1 already in the table. If one of your users were to try to create another customer with id 1, they would receive an error, and the update would be rejected because custid 1 is already listed in the pri- mary key’s unique index. Of course this is just for example, because your custid field has the identity property set, which automatically assigns a number with each new record inserted and will not allow you to enter a number of your own design. NOTE When a column can be used as a unique identifier for a row (such as an identity column), it is referred to as a surrogate or candidate key. The primary key should be made of a column (or columns) that contains unique values. This makes an identity column the perfect candidate for becoming a primary key, because the values contained therein are unique by definition. If you do not have an identity column, make sure to choose a column, or combination of columns, in which each value is unique. Since you have an identity column in the customers PA R T table, let’s use it to create a primary key: III 1. Open Enterprise Manager by selecting it from the SQL Server 2000 group in Pro- grams on your Start menu, expand your server, then expand databases. 2. Expand the Sales database and click Tables. Digging into SQL 3. Right-click the customers table and select Design Table. 4. In the table designer screen, right-click CustID under Column Name and select Set Primary Key. Server 5. Notice that just to the left of the CustID field, there is a small key icon denoting that this is the primary key.
  9. 428 CHAPTER 11 • TABLES 6. When you click the Save icon on the toolbar, SQL Server will create the unique index, which ensures that no duplicate values can be entered in the custid field. 7. Close the table designer. TI P When a column has mostly unique values, it is said to have high selectivity. When a column has several duplicate values, it is said to have low selectivity. Therefore the primary key field must have high selectivity (entirely unique values). That procedure was fairly simple, but suppose that you need to maintain entity integrity separately on more than one column. Perhaps you have an employees table with an employeeid field that has been set as the primary key, but you also have a Social Security number field on which you need to enforce entity integrity. Because you can have only one primary key per table, you would need to create a unique con- straint to enforce such entity integrity.
  10. RESTRICTING THE DATA 429 Using Unique Constraints There are two major differences between primary key constraints and unique con- straints. The first is that primary keys are used with foreign keys to enforce referential integrity (which we will discuss a little later in this chapter), and unique keys are not. The second difference is that unique constraints allow null (blank) values to be inserted in the field, whereas primary keys do not allow null values. Aside from that, they serve the same purpose—to ensure that unique data is inserted in a field. You should use a unique constraint when you need to ensure that no duplicate val- ues can be added to a field that is not part of your primary key. A good example of a field that might require a unique constraint is a Social Security number field, because all of the values contained therein need to be unique, yet there would most likely be a separate employee ID field that would be used as the primary key. Because you don’t really have a perfect candidate for a unique constraint in your tables, you will come as close as you can by creating a unique constraint on the Phone field: 1. While still in Enterprise Manager, right-click the customers table and select Design Table. 2. Right-click the Phone field and select Indexes/Keys. 3. Click the New button. 4. Under Column Name, select Phone. 5. In the Order box, select Ascending—this orders the index from lowest to highest values (i.e., one at the top and nine at the bottom, or A at the top and Z at the bottom). 6. In the Index Name box, type Unique_Phone. 7. Check the Create UNIQUE box. 8. Under Create UNIQUE, click the Constraint radio button. PA R T III Digging into SQL Server
  11. 430 CHAPTER 11 • TABLES 9. Click the Close button. 10. Click the Save icon on the toolbar. 11. Close the table designer screen. Now you can test the unique constraint by trying to add some duplicate phone numbers through Query Analyzer using some INSERT statements: 1. Open Query Analyzer by selecting it from the Tools menu in Enterprise Manager. 2. Enter and execute the following code to add a new record to the customers table: USE sales INSERT customers VALUES (‘Shane’,’Travis’,’806 Star’,’Phoenix’,’AZ’,’85202’,’6021112222’) 3. Try entering another customer with the same phone number by entering and executing the following: USE sales INSERT customers
  12. RESTRICTING THE DATA 431 VALUES (‘Janet’,’McBroom’,’5403 Western’,’Tempe’,’AZ’,’85103’,’6021112222’) 4. Notice that this failed, with a message that the UNIQUE constraint had been violated by the duplicate phone number. You now know how to protect the data that is entered in your tables by enforcing domain and entity integrity, but there is still one more area of integrity to consider. You need to know how to protect related data that is stored in separate tables by enforcing referential integrity. PA R T Enforcing Referential Integrity III You have three tables in your Sales database right now: one for customer data, one for product data, and one for order data. Each of these tables contains data that is affected by what is stored in one of your other tables. For instance, the orders table Digging into SQL is affected by the customers table in that you should not create an order for a cus- tomer that does not exist in your customers table. The orders table is also affected by the products table in that you do not want to create an order for a product that does not exist. If you want to make sure that a customer exists in your customers Server table before you sell them something, or if you do not want to sell nonexistent products, you need to enforce referential integrity.
  13. 432 CHAPTER 11 • TABLES Enforcing referential integrity does just what its name implies: Data in one table that refers to data in another table is protected from improper updating. In SQL Server terminology, the process of enforcing referential integrity is called declarative referential integrity (DRI), and it is accomplished by linking the primary key of one of your tables to a foreign key in another table. Let’s see what foreign keys do and how to create them. Using Foreign Keys A foreign key is used in combination with a primary key to relate two tables on a com- mon column. You could, for example, relate the orders table and the customers table on the custid column that they both have in common. If you use the custid field in the customers table as the primary key (which you already have), you can use the custid field in the orders table as the foreign key that relates the two tables. Now, unless you enable cascading referential integrity (which we’ll discuss shortly), you would not be able to add a record to the orders table if there is no matching record in the customers table. Not only that—you would not be able to delete a record in the customers table if there are matching records in the orders table, because you don’t want to have orders out there with no customer information. Before you see how this works, it is probably best to show you exactly what happens without referential integrity being enforced: 1. If you are still in Enterprise Manager, open Query Analyzer by selecting it from the Tools menu. 2. To insert a record with a customer ID, product ID, quantity, and current date (as reported by the GETDATE() function) in the orders table, enter and execute the following code: USE sales INSERT orders VALUES (999,5,57,getdate()) 3. Notice in the preceding step that you were successful even though there is no customer in the customers table with an ID of 999. 4. To remove the erroneous records, enter and execute the following code (note that this is a potentially dangerous command, because it deletes all records from a table): truncate table orders Now that you have proven that you can enter an order for a nonexistent customer, you need to protect your database against that. To do this, you will create a foreign key on the custid field of the orders table that relates to the custid field of the customers
  14. RESTRICTING THE DATA 433 table (which is the primary key of the customers table). With this relationship in place, your data will be protected across your tables. Let’s create that relationship: 1. Open Enterprise Manager, expand your server, expand databases, then click Tables under the Sales database. 2. Right-click the orders table and select Design Table. 3. Right-click the CustID field and select Relationships. 4. Click the New button to create a new relationship. 5. In the Primary Key drop-down list, select Customers. 6. In the Foreign Key drop-down list, select Orders. 7. In the table just below the Primary Key drop-down list, in the left side of the first line, select CustID as the primary-key column. 8. In the right side of the same table, just under the foreign-key drop-down box, select CustID as the foreign-key column. 9. In the Name box, type FK_Customers_Orders. 10. Leave the rest as defaults and click Close to create the relationship. 11. Click Yes when asked to save tables to the diagram (discussed later). PA R T III Digging into SQL Server
  15. 434 CHAPTER 11 • TABLES We’ll test that new relationship in just a moment—you are probably wondering what those checkboxes at the bottom of the dialog box were for, though. We’ll discuss the two at the very bottom of the dialog box a little later, but here are descriptions for three of them: Check Existing Data on Creation: The first checkbox is to instruct SQL Server to verify that all of the existing data in both tables fits the constraint parameters; if it does not, you will receive a warning instructing you to fix it. Enable Relationship for Replication: Replication is used for copying databases from one server to another. This option will enable the relationship to be copied via replication to another server along with the primary- and foreign-key tables. Enable Relationship for INSERTs and UPDATEs: If you find that you no longer need the relationship you have created, you can uncheck this box to disable it while leaving the relationship in place. This way, you do not need to completely re-create the relationship if you find that you need it again later. Now you are ready to test the new relationship. Here you will try to add some records to the orders table that have no corresponding record in the customers table, then you will try to delete a record from the customers table that references a record in the orders table: 1. To test the new foreign-key constraint, you will try to add the same record as in the last set of steps in Query Analyzer: USE sales INSERT orders VALUES (999,5,57,getdate()) 2. Notice that the addition failed because there is no customer number 999 in the customers table.
  16. RESTRICTING THE DATA 435 3. To make very sure that this is working, you will add a record to the orders table that has a matching customer number by executing the following code in a new query window: USE sales 4. Notice that the previous code was successful because customer 1 actually exists. 5. Now that you have a matching record in the orders table, let’s try to delete cus- tomer 1 from the customers table: USE sales DELETE from customers WHERE custid = 1 PA R T III Digging into SQL Server
  17. 436 CHAPTER 11 • TABLES Now you can see how the records in related tables are protected from improper updates. Users cannot add a record to a foreign-key table without a corresponding record in the primary-key table, and primary-key records cannot be deleted if they have matching foreign-key records. But wait, it gets even better: New with SQL Server 2000 is something called cascading referential integrity. Using Cascading Referential Integrity You just saw that the default behavior for a relationship is to prevent the addition or deletion of records in the related tables based on the existence of matching records. A record in a primary key cannot be deleted if there are corresponding records in the foreign-key table, for example. This behavior can be changed, however, by using cas- cading referential integrity.
  18. RESTRICTING THE DATA 437 You probably noticed the two checkboxes just under the Enforce Relationship for INSERTs and UPDATEs checkbox in the Create Relationship dialog box. Those two checkboxes control the behavior of cascading referential integrity: Cascade Update Related Fields: When this option is unchecked, you cannot change the value of a primary-key field if it has matching records in the foreign-key table. With this option checked, you can change the value of a primary-key field, and the matching foreign-key records will be automatically updated. Cascade Delete Related Records: With this option unchecked, you cannot delete a record from the primary-key table if there are corresponding foreign-key records. With this option checked, you can delete a record in the primary-key table, and all matching foreign-key records will be removed automatically. Let’s give this a try to demonstrate how it works. First, you need to disable the identity property of the custid field in the customers table, because you cannot manu- ally assign a value to a field with an identity property assigned to it, and you need to be able to do just that for a full test of cascading referential integrity. Once that process is finished, you will set both cascade options on your relationship and test the cascade capabilities: 1. Open Enterprise Manager, expand your server, expand databases, then click Tables under the Sales database. 2. Right-click the customers table and select Design Table. 3. Click the CustID field and, in the bottom half of the screen, set the identity property to No. 4. Click the Save button and click Yes when asked whether you want to save changes to the diagram. 5. Close the table designer and get back to Enterprise Manager. PA R T 6. Right-click the orders table and select Design Table. 7. Right-click the CustID field and select Relationships. III 8. At the bottom of the dialog box, check both of the options for cascading. Digging into SQL Server
  19. 438 CHAPTER 11 • TABLES 9. Click Close. 10. Click the Save icon on the toolbar and click Yes when asked to save changes to the diagram. 11. Close the table designer window by clicking the small X at the top right of the window. Now that you have enabled cascading referential integrity between the customers and orders tables, you are ready to test it from Query Analyzer: 1. Open Query Analyzer by selecting it from the Tools menu. 2. First you will verify the existing records in the customers and orders tables by entering and executing the following code (note that both lines are executed at the same time). You should see three customers and one order for custid 1 in the result sets: select * from customers select * from orders
  20. RESTRICTING THE DATA 439 3. To test the cascaded update feature, enter and execute the following code: UPDATE customers SET custid = 5 WHERE custid = 1 4. Enter and execute the same code from step 2 again. Notice that custid 1 has been changed to 5 in the customers and orders tables. PA R T III Digging into SQL Server
Đồng bộ tài khoản