title: Problems enforcing referential integrity on SQL Server tables
body: <p>I have a SQL Server 2005 database comprised of <code>Customers</code>, <code>Quote</code>, <code>QuoteDetail</code> tables.  I want/need to enforce referential integrity such that when an insert is made on <code>QuoteDetail</code>, the quote and customer tables are also affected.  I have tried my best to set up primary/foreign keys on my tables but need some help.  Here's the scripts for my tables as they stand now (please don't laugh):</p>
  
  <p>Customers:</p>
  
  <pre><code>CREATE TABLE [dbo].[Customers](
      [pkCustID] [int] IDENTITY(1,1) NOT NULL,
      [CompanyName] [nvarchar](50) NULL,
      [Address] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
      [State] [nvarchar](2) NULL,
      [ZipCode] [nvarchar](5) NULL,
      [OfficePhone] [nvarchar](12) NULL,
      [OfficeFAX] [nvarchar](12) NULL,
      [Email] [nvarchar](50) NULL,
      [PrimaryContactName] [nvarchar](50) NULL,
  
      CONSTRAINT [PK_Customers] 
        PRIMARY KEY CLUSTERED([pkCustID] ASC)
  )
  </code></pre>
  
  <p>Quotes:</p>
  
  <pre><code>CREATE TABLE [dbo].[Quotes](
      [pkQuoteID] [int] IDENTITY(1,1) NOT NULL,
      [fkCustomerID] [int] NOT NULL,
      [QuoteDate] [timestamp] NOT NULL,
      [NeedbyDate] [datetime] NULL,
      [QuoteAmt] [decimal](6, 2) NOT NULL,
      [QuoteApproved] [bit] NOT NULL,
      [fkOrderID] [int] NOT NULL,
  
      CONSTRAINT [PK_Bids] 
         PRIMARY KEY CLUSTERED ([pkQuoteID] ASC)
  ) 
  GO
  
  ALTER TABLE [dbo].[Quotes] WITH CHECK 
     ADD CONSTRAINT [fkCustomerID] 
     FOREIGN KEY([fkCustomerID]) REFERENCES [dbo].[Customers] ([pkCustID])
  </code></pre>
  
  <p>QuoteDetail:</p>
  
  <pre><code>CREATE TABLE [dbo].[QuoteDetail](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [fkQuoteID] [int] NOT NULL,
  [fkCustomerID] [int] NOT NULL,
  [fkPartID] [int] NULL,
  [PartNumber1] [float] NOT NULL,
  [Qty1] [int] NOT NULL,
  [PartNumber2] [float] NULL,
  [Qty2] [int] NULL,
  [PartNumber3] [float] NULL,
  [Qty3] [int] NULL,
  [PartNumber4] [float] NULL,
  [Qty4] [int] NULL,
  [PartNumber5] [float] NULL,
  [Qty5] [int] NULL,
  [PartNumber6] [float] NULL,
  [Qty6] [int] NULL,
  [PartNumber7] [float] NULL,
  [Qty7] [int] NULL,
  [PartNumber8] [float] NULL,
  [Qty8] [int] NULL,
  [PartNumber9] [float] NULL,
  [Qty9] [int] NULL,
  [PartNumber10] [float] NULL,
  [Qty10] [int] NULL,
  [PartNumber11] [float] NULL,
  [Qty11] [int] NULL,
  [PartNumber12] [float] NULL,
  [Qty12] [int] NULL,
  [PartNumber13] [float] NULL,
  [Qty13] [int] NULL,
  [PartNumber14] [float] NULL,
  [Qty14] [int] NULL,
  [PartNumber15] [float] NULL,
  [Qty15] [int] NULL,
  [PartNumber16] [float] NULL,
  [Qty16] [int] NULL,
  [PartNumber17] [float] NULL,
  [Qty17] [int] NULL,
  [PartNumber18] [float] NULL,
  [Qty18] [int] NULL,
  [PartNumber19] [float] NULL,
  [Qty19] [int] NULL,
  [PartNumber20] [float] NULL,
  [Qty20] [int] NULL,
      CONSTRAINT [PK_QuoteDetail] 
         PRIMARY KEY CLUSTERED([ID] ASC)
  )
  GO
  
  ALTER TABLE [dbo].[QuoteDetail] WITH CHECK 
    ADD CONSTRAINT [FK_QuoteDetail_Customers] 
    FOREIGN KEY([fkCustomerID]) REFERENCES [dbo].[Customers] ([pkCustID])
  
  ALTER TABLE [dbo].[QuoteDetail] WITH CHECK 
     ADD CONSTRAINT [FK_QuoteDetail_PartList] 
     FOREIGN KEY([fkPartID]) REFERENCES [dbo].[PartList] ([RecID])
  
  ALTER TABLE [dbo].[QuoteDetail] WITH CHECK 
     ADD CONSTRAINT [FK_QuoteDetail_Quotes] 
     FOREIGN KEY([fkQuoteID]) REFERENCES [dbo].[Quotes] ([pkQuoteID])
  </code></pre>
  
  <p>Your advice/guidance on how to set these up so that customer ID in Customers is the same as in Quotes (referential integrity) and that CustomerID is inserted on Quotes and Customers when an insert is made to QuoteDetial would be much appreciated.</p>
  
  <p>Update:  Made table changes, here's the sproc now:</p>
  
  <pre><code>USE [Diel_inventory]
  GO
  /****** Object:  StoredProcedure [dbo].[AddQuote]    Script Date: 05/08/2010 14:57:28 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER procedure [dbo].[AddQuote] as
  Declare @CustID int,
  @CompanyName nvarchar(50),
  @Address nvarchar(50),
  @City nvarchar(50),
  @State nvarchar(2),
  @ZipCode nvarchar(5),
  @Phone nvarchar(12),
  @FAX nvarchar(12),
  @Email nvarchar(50),
  @ContactName nvarchar(50),
  @QuoteID int,
  @QuoteDate datetime,
  @NeedbyDate datetime,
  @QuoteAmt decimal,
  @ID int,
  @QuoteDetailPartID int,
  @PartNumber float,
  @Quantity int
  
  begin
  
  Insert into dbo.Customers
  (CompanyName, Address, City, State, ZipCode, OfficePhone, OfficeFAX, Email, PrimaryContactName)
  Values (@CompanyName, @Address, @City, @State, @ZipCode, @Phone, @FAX, @Email, @ContactName)
  
  set @CustID = scope_identity()
  
  
  Insert into dbo.Quotes 
  (fkCustomerID,NeedbyDate,QuoteAmt)
  Values(@CustID,@NeedbyDate,@QuoteAmt)
  
  set @QuoteID = scope_identity() 
  
  
  Insert into dbo.QuoteDetail
  (ID) values(@ID)
  
  set @ID=scope_identity()
  
  
  
  Insert into dbo.QuoteDetailParts
  (QuoteDetailPartID, QuoteDetailID, PartNumber, Quantity)
  values (@ID, @QuoteDetailPartID, @PartNumber, @Quantity) 
  END
  </code></pre>
  
  <p>In SSMS, the parameters are not visible in the treeview of this sproc.  Also, when I try calling this sproc from my ASPX page, I receive </p>
  
  <pre><code>System.Data.SqlClient.SqlException: Procedure AddQuote has no parameters and arguments were supplied
  </code></pre>
  
  <p>Here's the SQLDataSource defined on my ASPX page:</p>
  
  <pre><code>&lt;asp:SqlDataSource runat="server" ID="SDSAddQuote" 
      ConnectionString="&lt;%$ ConnectionStrings:Diel_inventoryConnectionString %&gt;" 
      InsertCommand="AddQuote" InsertCommandType="StoredProcedure"&gt;
      &lt;InsertParameters&gt;
      &lt;asp:ControlParameter ControlID="txtCompanyName" type="String" Name="CompanyName" /&gt;
      &lt;asp:ControlParameter ControlID="txtCompanyAddress" Type="String" Name="CompanyAddress" /&gt;
      &lt;asp:ControlParameter ControlID="txtCompanyCity" Type="String" Name="CompanyCity" /&gt;
      &lt;asp:ControlParameter ControlID="ddlCompanyState" Type="String" Name="CompanyState" /&gt;
      &lt;asp:ControlParameter ControlID="txtCompanyZip" Type="String" Name="CompanyZip" /&gt;
      &lt;asp:ControlParameter ControlID="calNeedByDate" Type="DateTime" Name="NeedByDate" /&gt;
      &lt;asp:ControlParameter ControlID="txtPartNumber1" Type="String" Name="PartNumber1" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty1" Type="Int16" Name="Qty1" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber2" Type="String" Name="PartNumber2" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty2" Type="Int16" Name="Qty2" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber3" Type="String" Name="PartNumber3" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty3" Type="Int16" Name="Qty3" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber4" Type="String" Name="PartNumber4" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty4" Type="Int16" Name="Qty4" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber5" Type="String" Name="PartNumber5" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty5" Type="Int16" Name="Qty5" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber6" Type="String" Name="PartNumber6" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty6" Type="Int16" Name="Qty6" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber7" Type="String" Name="PartNumber7" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty7" Type="Int16" Name="Qty7" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber8" Type="String" Name="PartNumber8" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty8" Type="Int16" Name="Qty8" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber9" Type="String" Name="PartNumber9" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty9" Type="Int16" Name="Qty9" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber10" Type="String" Name="PartNumber10" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty10" Type="Int16" Name="Qty10" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber11" Type="String" Name="PartNumber11" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty11" Type="Int16" Name="Qty11" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber12" Type="String" Name="PartNumber12" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty12" Type="Int16" Name="Qty12" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber13" Type="String" Name="PartNumber13" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty13" Type="Int16" Name="Qty13" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber14" Type="String" Name="PartNumber14" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty14" Type="Int16" Name="Qty14" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber15" Type="String" Name="PartNumber15" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty15" Type="Int16" Name="Qty15" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber16" Type="String" Name="PartNumber16" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty16" Type="Int16" Name="Qty16" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber17" Type="String" Name="PartNumber17" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty17" Type="Int16" Name="Qty17" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber18" Type="String" Name="PartNumber18" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty18" Type="Int16" Name="Qty18" /&gt;
              &lt;asp:ControlParameter ControlID="txtPartNumber19" Type="String" Name="PartNumber19" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty19" Type="Int16" Name="Qty19" /&gt;     
             &lt;asp:ControlParameter ControlID="txtPartNumber20" Type="String" Name="PartNumber20" /&gt;
      &lt;asp:ControlParameter ControlID="txtQty20" Type="Int16" Name="Qty20" /&gt;   
      &lt;/InsertParameters&gt;
  &lt;/asp:SqlDataSource&gt;
  </code></pre>
  
  <p>Why aren't my parameters available, despite declaring them at the start of my sproc?<br>
  Thanks,
  Sid</p>