Problem Setting Foreign Key

Question

I am trying to set a foreign key relationship between an Order_Items table and Parts table. I want to link the parts to products in the Order_Items table via foreign key. I have no issues doing this with other tables.

Here is how the Order_Items table is defined:

CREATE TABLE [dbo].[Order_Items](
    [order_id] [uniqueidentifier] NOT NULL,
    [product_number] [varchar](50) NOT NULL,
    [quantity_ordered] [int] NOT NULL,
    [product_tested] [bit] NULL,
 CONSTRAINT [PK_Order_Items] PRIMARY KEY CLUSTERED 
(
    [order_id] ASC,
    [product_number] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Order_Items]  WITH CHECK ADD  CONSTRAINT [FK_Order_Items_Orders] FOREIGN KEY([order_id])
REFERENCES [dbo].[Orders] ([order_id])
GO
ALTER TABLE [dbo].[Order_Items] CHECK CONSTRAINT [FK_Order_Items_Orders]

and Parts table:

CREATE TABLE [dbo].[Parts](
    [part_number] [varchar](50) NOT NULL,
    [product_number] [varchar](50) NOT NULL,
    [part_description] [varchar](max) NULL,
    [part_tested] [bit] NULL,
 CONSTRAINT [PK_Parts_1] PRIMARY KEY CLUSTERED 
(
    [part_number] ASC,
    [product_number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I have tried setting the unique constraint on both product_number columns, but I still get the error message in SQL Server 2005 Management Studio Express as:

"The columns in table 'Order_Items' do not match an existing primary key or UNIQUE constraint"

Answer 1

Question: if [product_number] in your Parts table can be made unique by a unique constraint - why isn't it the primary key by itself??

My gut feeling: [product_number] in Parts isn't really unique - only the combination of (part_number, product_number), which is the primary key, really is unique.

If you can create a UNIQUE INDEX on the product_number column alone, you should be able to create the FK constraint - try this:

CREATE UNIQUE INDEX UIX01_Parts ON dbo.Parts(product_number)

ALTER TABLE dbo.Order_Items
  ADD CONSTRAINT FK_OrderITems_Parts
    FOREIGN KEY(product_number) REFERENCES dbo.Parts(product_number)

Does it work? If not - what error to you get, and where??

If that doesn't work, and only (part_number, product_number) is truly unique, then you need to reference both columns in your foreign key constraint:

ALTER TABLE dbo.Order_Items
  ADD CONSTRAINT FK_OrderItems_Parts
  FOREIGN KEY(part_number, product_number)
    REFERENCES dbo.Parts(part_number, product_number)

and of course, this also means you need to have both those columns in your Order_Items table in order to be able to make the foreign key constraint work.

Just as a side note: having a compound primary key of two VARCHAR(50) columns and making that a clustered index on your Parts table is anything but optimal. If ever possible, try to make one or both of those "numbers" really a numeric type - e.g. an INT column. Or if that is not possible, think about having a surrogate PartID column (INT, IDENTITY) on your Parts table - that would make the FK constraint easier, too!

Answer 2

In order for this relationship to work, the Parts table cannot have composite key. In other words, you need to use product_number as the primary key since it is the column that they both have in common. You currently have part_number and product_number as your primary key.

Once you make that change, this statement will work:

ALTER TABLE [dbo].[Order_Items]  WITH CHECK 
ADD  CONSTRAINT [FK_Order_Items_Parts] FOREIGN KEY([product_number])
REFERENCES [dbo].[Parts] ([product_number])

Answer 3

I ended up restructuring my entire database to get this to work and streamlined the table connections.