Problems Enforcing Referential Integrity on SQL Server Tables
- by SidC
Hello All,
I have a SQL Server 2005 database comprised of Customer, Quote, QuoteDetail tables. I want/need to enforce referential integrity such that when an insert is made on quotedetail, 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):
Customers:
USE [Diel_inventory]
GO
/****** Object: Table [dbo].[Customers] Script Date: 05/08/2010 03:39:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Quotes:
USE [Diel_inventory]
GO
/****** Object: Table [dbo].[Quotes] Script Date: 05/08/2010 03:30:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Quotes] WITH CHECK ADD CONSTRAINT [fkCustomerID] FOREIGN KEY([fkCustomerID])
REFERENCES [dbo].[Customers] ([pkCustID])
GO
ALTER TABLE [dbo].[Quotes] CHECK CONSTRAINT [fkCustomerID]
QuoteDetail:
USE [Diel_inventory]
GO
/****** Object: Table [dbo].[QuoteDetail] Script Date: 05/08/2010 03:31:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_Customers] FOREIGN KEY ([fkCustomerID])
REFERENCES [dbo].[Customers] ([pkCustID])
GO
ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_Customers]
GO
ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_PartList] FOREIGN KEY ([fkPartID])
REFERENCES [dbo].[PartList] ([RecID])
GO
ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_PartList]
GO
ALTER TABLE [dbo].[QuoteDetail] WITH CHECK ADD CONSTRAINT [FK_QuoteDetail_Quotes] FOREIGN KEY([fkQuoteID])
REFERENCES [dbo].[Quotes] ([pkQuoteID])
GO
ALTER TABLE [dbo].[QuoteDetail] CHECK CONSTRAINT [FK_QuoteDetail_Quotes]
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.
Thanks,
Sid
