Problems adding foreign key

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  July 30, 2008 at 7:11 am

  #382053

  Have two tables, ptPayments and ptPaymentBatches.

  Both have column PaymentBatchID and all values in ptPayments are NULL.

  In ptPaymentBatches, it is the Primary Key.

  I am creating ptPaymentBatches and need to set a foreign key into the

  ptPayments table

  However, this

  ALTER TABLE [dbo].[ptPaymentBatches]

  WITH CHECK

  ADD CONSTRAINT [FK_ptPaymentBatches_ptPayments]

  FOREIGN KEY([PaymentBatchID])

  REFERENCES [dbo].[ptPayments] ([PaymentBatchID])

  gives me this error:

  Msg 1776, Level 16, State 0, Line 1

  There are no primary or candidate keys in the referenced table 'dbo.ptPayments'

  that match the referencing column list in the

  foreign key 'FK_ptPaymentBatches_ptPayments'.

  Msg 1750, Level 16, State 0, Line 1

  Could not create constraint. See previous errors.

  Not sure why, is it that I should be adding the FK to the ptPyments table instead?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 30, 2008 at 7:36 am

  #850492

  Yep, spot on Mr Baseball.

  /* table definitions for testing =====================

  -- Payment batch table ==========================

  CREATE TABLE [dbo].[ptPaymentBatches] (

  [PaymentBatchID] [int] IDENTITY (1, 1) NOT NULL

  ) ON [PRIMARY]

  CREATE UNIQUE CLUSTERED INDEX [PaymentBatchID] ON [dbo].[ptPaymentBatches]([PaymentBatchID]) ON [PRIMARY]

  -- Payments table ==========================

  CREATE TABLE [dbo].[ptPayments] (

  [PaymentID] [int] IDENTITY (1, 1) NOT NULL ,

  [PaymentBatchID] [int] NULL

  ) ON [PRIMARY]

  CREATE UNIQUE CLUSTERED INDEX [PaymentID] ON [dbo].[ptPayments]([PaymentID]) ON [PRIMARY]

  CREATE INDEX [PaymentBatchID] ON [dbo].[ptPayments]([PaymentBatchID]) ON [PRIMARY]

  */

  -- Put a couple of rows into the table

  INSERT INTO [dbo].[ptPayments] ([PaymentBatchID]) VALUES (NULL)

  INSERT INTO [dbo].[ptPayments] ([PaymentBatchID]) VALUES (NULL)

  SELECT * FROM [dbo].[ptPayments]

  -- FK constraint ==========================

  ALTER TABLE [dbo].[ptPayments]

  WITH CHECK

  ADD CONSTRAINT [FK_ptPaymentBatches_ptPayments]

  FOREIGN KEY([PaymentBatchID])

  REFERENCES [dbo].[ptPaymentBatches] ([PaymentBatchID])

  -- try to insert a value into ptPayments.PaymentBatchID with no matching value in ptPaymentBatches.PaymentBatchID

  INSERT INTO [dbo].[ptPayments] ([PaymentBatchID]) VALUES (1)

  -- INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_ptPaymentBatches_ptPayments'.....

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  July 30, 2008 at 7:42 am

  #850494

  Thanks, will make that recommendation.