|
[ Pobierz całość w formacie PDF ]
.6.Listing 7.6.The Primary Key Constraint for the ProductsPurchased Table1: ALTER TABLE ProductsPurchased2: ADD CONSTRAINT ProductsPurchasedIndex3: PRIMARY KEY (ordernumber, partnumber)The code in Listing 7.6 adds a primary key constraint to the ProductsPurchased table.Theprimary key is a composite key consisting of the OrderNumber field and the PartNumber field.The constraint will prevent duplicate OrderNumber/PartNumber combinations among the recordsin the table.Relational databases use indexes to optimize the performance of data access operations.If you merelycreate tables and do not use indexes, the database will be forced to perform table scans.The databasewill start at the beginning of the table and sequentially look at every record until it finds the record(s)it needs.If, on the other hand, you create indexes for your tables, the database can look up the value itis searching for in the index and move directly to the appropriate record(s).The primary key is indexed.When you specify a primary key on a table, the database creates an indexfor the table using the primary key.If you will frequently use other fields in queries, such as in the WHERE clause or the ORDER BYclause of SELECT statements, you will probably want to create indexes for those fields as well.Youcan create as many indexes as you need for each table (within practical limits).The following is thesyntax for creating an index:CREATE INDEX myIndex ON myTable (myField)You should use indexes only where they are needed.They will reduce insert, update, and delete performance because every time you change an indexed field in a record, the database has to updatethe index as well.Tools and Techniques for Managing Relationships in aRelational DatabaseYou learned earlier today that you should carefully identify the one-to-one, one-to-many, andmany-to-many relationships in your database designs (see R2D2 #3).To model a one-to-one relationship in your database, use primary keys and foreign keys as you learnedin Day 2, "Tools for Database Development in Visual C++ Developer Studio," and Day 3, "RetrievingData Through Structured Query Language (SQL)." For every instance of the primary key in one table,you will have no more than one instance of the foreign key in the foreign table.To model a one-to-many relationship, use primary keys and foreign keys as you learned in Day 2 andDay 3.For every instance of the primary key in one table, you can have any number of instances of theforeign key in the foreign table.Modeling many-to-many relationships requires that you create a third table.The two tables that youwant to relate will contain their primary keys (as you would expect).The third table, called the linktable, will contain the foreign keys from both primary tables.This is best understood through anexample.You will recall that the design of the original Orders table contained the product number and thecustomer number as foreign keys (see Figure 7.5).The original Orders table was a link table that facilitated a many-to-many relationship betweencustomers and products.You could perform a join between these three tables and find out whichcustomers bought which products.This is an excellent example of a many-to-many relationshipbecause a single customer could buy many products and many customers could buy a single product.Figure 7.5 is a simple entity relationship diagram (ER diagram) that shows the relationship betweenthese three tables.The Customers table and the Products table contain the primary keys.TheOrders table contains the foreign keys, so it is the link table.You can see that lines run between the primary and foreign key fields.A 1 is next to the primary keysand an infinity sign next to the foreign key fields.This is due to the one-to-many relationship betweenthe primary keys in the Customers and Products tables and the foreign keys in the link(Orders) table.When the one-to-many relationships are combined in the link table, it produces amany-to-many relationship between the Customers and Products tables.Figure 7.5 : Many-to-many relationships. Using Constraints to Enforce RelationshipsYou can place constraints on the database that enforce the relationships.These constraints prevent auser from deleting a record whose primary key constitutes a foreign key in another table.Referential integrity constraints are constraints that ensure that the data in one table is consistent withdata in other tables in the database.A referential integrity constraint will prevent you from deleting a product from the Products tablethat is listed in the ProductsPurchased table.You will recall that you encountered a constraintlike this in Day 6, "Harnessing the Power of Relational Database Servers," when you tried to delete allthe 8-track products from the Products table.This is because that delete operation would have leftorphaned records in the Orders table.To create a referential integrity constraint, you can use the ALTER TABLE statement with the ADDCONSTRAINT clause, as shown in Listing 7.7.Listing 7.7.The Foreign Key Constraint Between the ProductsPurchased and Products Tables1: ALTER TABLE ProductsPurchased2: ADD CONSTRAINT fk_partnumber3: FOREIGN KEY (PartNumber)4: REFERENCES Products (PartNumber)Listing 7.7 creates a constraint to enforce the referential integrity between theProductsPurchased table and the PartNumber table.Line 2 in Listing 7
[ Pobierz całość w formacie PDF ]
zanotowane.pldoc.pisz.plpdf.pisz.plhanula1950.keep.pl
|