ansaurus

Question

Problems enforcing referential integrity on SQL Server tables

Answer 1

+2 A:

What exactly is it you're trying to do??

I want/need to enforce referential integrity such that when an insert is made on quotedetail, the quote and customer tables are also affected.

What do you mean by "...quote and customer table are also affected". Affected how??

Referential integrity by using foreign key means you cannot insert a Quote with a value of fkCustomerID that cannot be found in the Customers table.

Likewise, you cannot insert a QuoteDetail which has a value of fkQuoteID that is not found in the Quote table.

So what do you want to do when a QuoteDetail is inserted? How should the two other tables be "affected" ??

A side note: since your QuoteDetail table references the Quote table, which in turn references the Customers table, there's really no point in having the fkCustomerId in the QuoteDetails. I assume if a Quote is assigned to customer no. 12345, then all the QuoteDetails that belong to that quote will also be relevant for customre no. 12345, right?? So just reference Quote from QuoteDetails, and that's plenty good enough! Otherwise, you're just overcomplicating your model without gaining anything, really.

Side note #2: I would strongly recommend you get rid of those twenty pairs of PartNumberX / QtyX in your QuoteDetail - put those into a separate table, e.g. QuoteDetailParts, which references the QuoteDetail detail and has

QuoteDetailPartID    INT   PK
QuoteDetailID        INT   FK  --> to QuoteDetail table
PartNumber           FLOAT
Quantity             INT

as its fields. That's the much cleaner approach and design - this will allow you to store just two or three parts, if needed - but also 21, 25 if you ever need to - without changing the database!

UPDATE: if you want to insert values into QuoteDetail, you need to first have an entry in Quote and thus an entry in Customers. In your stored proc, you can do something like:

CREATE PROCEDURE dbo.InsertQuoteDetail
  (define list of parameters you want to pass in)
AS BEGIN
  DECLARE @CustomerID INT

  INSERT INTO dbo.Customers(list of fields)
    VALUES(list of values)

  SET @CustomerID = SCOPE_IDENTITY()

  DECLARE @QuoteID INT

  INSERT INTO dbo.Quote(CustomerID, (list of other fields))
     VALUES(@CustomerID, ....other values.....)

  SET @QuoteID = SCOPE_IDENTITY()

  INSERT INTO dbo.QuoteDetails(QuoteID, ....other fields....)
     VALUES(@QuoteID, ...... other values......)

marc_s 2010-05-08 08:31:18

@marc_s: Thanks for your reply, nightowls rule! :) When a new record is inserted in QuoteDetail, I want Quotes and Customers tables both updated. A new CustomerID is inserted into Customers and that is also referenced in Quotes. Likewise, a new QuoteID is inserted into Quotes and referenced in QuoteDetail. Please let me know if this doesn't make sense, I'll take another stab at it in the morning. Thanks!

SidC 2010-05-08 08:38:18

@SidC: it's 10:40am on Saturday here :-) Morning birds rule! :-)

marc_s 2010-05-08 08:40:23

@SIdC: you cannot do that - you would have to insert Customer first, make a note of the new CustomerID; then insert the Quote settnig its fkCustomerID to the newly inserted CustomerID, make a note of QuoteID; and then lastly insert QuoteDetails as needed, referencing just the Quote table using the newly created QuoteID. That's what referential integrity is all about.

marc_s 2010-05-08 08:41:40

@marc_s: So, in my sproc, I need to insert fields into the Customer table first, but how do I get the CustomerID and can I grab it from the sproc? Then, can I do the insert into the Quote table from the same sproc? Likewise for QuoteDetails and QuoteDetailParts? Thanks much:)

SidC 2010-05-08 08:45:04

@SidC: yes, you need to insert values into the Customers table first. You can get the new customer id using `CustomerID = SCOPE_IDENTITY()` and likewise for the Quote table.

marc_s 2010-05-08 10:31:40

@marc_s: You should not be inserting anything into the `Customers` and `Quotes` tables in your `dbo.InsertQuoteDetail` sproc.

FreshCode 2010-05-09 13:38:42

@FreshCode: the OP **specifically** asked how he could insert something directly into the QuoteDetail table - that won't work unless you **first** insert a customer and next a Quote. But I agree - I don't like that approach either, but if you insist on doing this, that's the only way to do it, really.

marc_s 2010-05-09 13:57:55

Answer 2

+1 A:

Fix your `QuoteDetail` table design.

Your referential integrity will take care of itself if you design your database correctly. Refer to this good example on the third-normal form (3NF).

Having 20 "PartNumber" columns per record limits each quote to 20 items, complicates quote calculations, is error-prone and duplicates data.

Your Customers table is OK, but
Quotes should not have a QuoteAmt column: this should be calculated from your QuoteDetail table.
Since QuoteDetail is related to Quotes, which references a CustomerID, you don't need to store CustomerID in QuoteDetail again.

Any data that is "referenced", should always come from the referenced table. A customer's name should be provided by the Customers table; it should not be stored in your Quotes table. Likewise, Quotes should not store its (quote) total, because that value depends on the data in QuoteDetail.

Here is a better design for `QuoteDetail`:

CREATE TABLE [dbo].[QuoteDetail](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fkQuoteID] [int] NOT NULL,   // which Quote this line/detail belongs to
    [fkPartID] [int] NOT NULL,    // which Part this line describes
    [Quantity] [int] NOT NULL,    // how many Parts
    [UnitPrice] [float] NOT NULL  // how much per Part
)

Now you can calculate each LineTotal as Quantity * UnitPrice, which you then sum to calculate your quote total (QuoteAmt). This design will ensure that your quote total is always correct and no data is ever duplicated. You can (and should) use views to calculate these values for display.

Refer to my question on database design for invoices and quotes (which are very similar to invoices). Ignore the part about revisions and look at the table design.

FreshCode 2010-05-08 11:54:03

Thanks much! Would ID in QuoteDetail be a PK?

SidC 2010-05-08 17:33:06

**Yes**. You could use `fkQuoteID` and `fkPartID` as a combined key, but then you could not have two of the same parts at different unit prices (which may or may not be appropriate for your domain problem). To stay flexible, I would not recommend it. That approach has merit for shopping cart items where you may only want to adjust the quantity. A single PK also makes deletes and updates more elegant, since you only have to deal with one parameter.

FreshCode 2010-05-09 13:36:25

I went with just the one PK in this table. Another question for you: My ASPX form has 20 textbox controls for PartNumber and 20 textbox controls for Quantity. How do I go about sending the values of these to the database? That is, what insert syntax would I use? e.g for each txtPartNumber (in 1 to 20) insert txtPartNumber.text values (@PartNumber) ?

SidC 2010-05-09 20:04:03

Get input working for adding **one line** to a quote first before trying to do more. If your `QuoteDetails` table references your `Parts` table (as opposed to generic lines with text descriptions), then you don't want the user to provide `txtPartName`. It should be pulled from your `Parts` table for display. Provide the user with a parts list which only submits the part ID, eg. a drop-down or autocomplete field. Once you have that working, you could use an input ID attribute naming schema of `line[n].ID`, `line[n].Quantity` and optionally `line[n].UnitPrice` for multiple lines.

FreshCode 2010-05-09 20:51:39

also see http://blog.stevensandrson.com/2010/01/28/editing-a-variable-length-list-aspnet-mvc-2-style/

FreshCode 2010-05-09 22:19:33

ansaurus

tags:

views:

answers:

Problems enforcing referential integrity on SQL Server tables

Fix your `QuoteDetail` table design.

Here is a better design for `QuoteDetail`:

related questions

ansaurus

tags:

views:

answers:

Problems enforcing referential integrity on SQL Server tables

Fix your QuoteDetail table design.

Here is a better design for QuoteDetail:

related questions

Fix your `QuoteDetail` table design.

Here is a better design for `QuoteDetail`: