Hi, The invoice database shows the OrderDetails table as having two primary keys? The instructions say to have the OrderID column as the foreign key? If you add a foreign key contraint to the OrderID column the "key icon" does not appear? But the key icon is showing for both the OrderID and LineItem columns for the OrderDetails table? What am I missing here?

A table can only have one primary key but multiple foriegn keys. In the Management Studio the primary key of a table is displayed as a gold color and foriegn keys are displayed as grey.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

HI,
The book is in black and white so that might be the case. However I do not get the key icon showing "OrderID" field as a foreign key. I manually entered the constraint below.

ALTER TABLE OrderDetails
ADD CONSTRAINT FK_OrderDetailsFROMOrders
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)

 However I do have a 'grey key' in the database view pane under 'keys' for the OrderDetails table. It also shows that both the OrderID field and PartNo as being foreign keys. I did not enter a constraint for the PartNo? Also chapter 8 does not have any chapter specific code like most of the other chapters.

Ok I am looking at the book on Book 24x7. In your Orders Table your Primary Key is columnL OrderID. In your OrderDetails Table column LineItem is your Primary Key and OrderDetails is a Foreign key (that relates back to orders)

PartNo is a Foreign Key to the Products table.

Regarding the Relationships, after Rob talks about the products and customers table the heading: Adding the Relationships follows thereafter.

Read through this section as, it appears, he adds the relationships to the table through the database diagram and not through actual SQL Code. (Although, code like you provided above is run in the background)

I hope that helps but, if not, post back and I will do my best. =]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

Ok,
I have re established the relationships on all the tables according to chapter 8 'Adding the Relationships' At no time do the 'foreign key' icons appear for any of the foreign key's. This leads me to belive that both the OrderID and LineItem fields are being used as a composite primary key for the OrderDetails table. That explains why there are two 'key icons' for the OrderDetails table. I just don't totally understand why the need for two primary keys when a similar key such as a 'OrderDetailsID' might be used?

Ok, I have gone in and recreated this Database on my local machine based upon that chapter. (I just didnt add the constraint on partNo with the RegEx)

Here is the issue I think that you are having: when you are working with a diagram and adding relationships and such, UNTIL you click the Save Icon, none of the relationships that you have setup will actually be applied to the table. (Which I assume you know) But, after clicking save, the Management Studio does not refresh your Object Browser pane. So, after you have created your diagram, click save then go over to yoru Object Browser and right click on your tables folder in the Invoice Database and select refresh. After that, expand the 'Keys' folder for each of the tables and you should see all of the appropriate Primary and Foreign Keys.

In so far as the Composite Key is concerned, I beleive you are correct. He states: "The LineItem is going to start over again with each row" which makes sense since an invoice will have a number of line items, 1, 2, 3 etc. and each invoice will begin back at 1. In this secnario, LineItem and OrderID combine to form the Primary Key. (It would be the only way to get a distinct item from an order)

Lastly, had I designed the table it probably would of had a layout something like this (I name my PK columsn pk in my database designs, just FYI):

OrderDetails
pk int identity auto inc
OrderID int FK_to_Orders
LineItem int
PartNo char(6) FK_to_Products
Qty int
UnitPrice money

As you can see, this table design has 1 primary key and 2 Foreign Keys. LineItem in this table design would be more a descriptor of the Order then anything else and this type of design also eliminates the need for a composite key.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

Hi and thanks for the explanation. I don't remember seeing the 'auto inc' (auto increment) parameter when setting the idenity property. That must be set automatically when using the design view instead of the 'ALTER TABLE' method.

In SQL Server, it's called an identity column. See p. 123 of your text.

An identity column may or may not have the value Auto Inc set. That is just how i set up my tables.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========