none
foreign key constraint on multiple (but not all) columns of a composite key

    Question

  • I have a composite primary key on TableA with 6 columns. TableB has a composite primary key of 2 columns. I am trying to place a foreign key on TableA that references the two primary key columns on TableB. I get this error "There are no primary or candidate keys in the referenced table 'TABLEB' that match the referencing column list in the foreign key 'FK1'.

    I have another Table, TableC that has a composite key with 7 columns. 6 of those 7 columns are referencing all of TableA's composite primary key columns. This constraint works.  Table C has multiple columns (not all) of a composite key as a foreign key constraint but I cannot do the same for Table A. What is the logic behind this?

    Friday, March 04, 2011 4:25 PM

Answers

  • Read the error message completely and closely.  The message is:

    Server: Msg 1776, Level 16, State 1, Line 2
    There are no primary or candidate keys in the referenced table 'TABLE_B' that match the referencing column list in the foreign key 'FK1'.
    Server: Msg 1750, Level 16, State 1, Line 2
    Could not create constraint. See previous errors.

    Notice the bit about "column list".  What is the order of columns in the primary key of TABLE_B?

    • Marked as answer by Jeff Rix Friday, March 04, 2011 6:53 PM
    Friday, March 04, 2011 6:07 PM

All replies

  • If I understand you correctly, what you are attempting should work.   Can you post the code you used to create TableB and to create the FK1 on TableA?    Maybe you didn't actually create a PK on TableB, or maybe you aren't referencing the right columns in your FK.   The error is saying you don't have a unique key (on TableB) on the columns you are trying to reference.


    -Tab Alleman
    Friday, March 04, 2011 4:42 PM

  • CREATE TABLE [dbo].[TABLE_B](
     [MINOR_NAME] [varchar](50) NOT NULL,
     [APPLICATION] [varchar](10) NOT NULL,
     [CHANGE_DATETIME] [datetime] NULL,
     [CHANGE_UID] [varchar](100) NULL,
     [CREATE_DATETIME] [datetime] NOT NULL,
     [CREATE_UID] [varchar](100) NOT NULL,
     CONSTRAINT [PK_TABLE_B] PRIMARY KEY CLUSTERED
    (
     [MINOR_NAME],
     [APPLICATION]
    )
    )
    ;
    GO

    CREATE TABLE [dbo].[TABLE_A](
     [SCHOOL_YEAR] [int] NOT NULL,
     [APPLICATION] [varchar](10) NOT NULL,
     [MINOR_NAME] [varchar](50) NOT NULL,
     [MARKING_PERIOD] [varchar](5) NOT NULL,
     [BUILDING] [int] NOT NULL,
     [GRADE] [nvarchar](2) NOT NULL,
     [CREATE_DATETIME] [datetime] NOT NULL,
     [CREATE_UID] [varchar](100) NOT NULL,
     [CHANGE_DATETIME] [datetime] NULL,
     [CHANGE_UID] [varchar](100) NULL,
     CONSTRAINT [PK_TABLE_A] PRIMARY KEY CLUSTERED
    (
     [SCHOOL_YEAR] ASC,
     [APPLICATION] ASC,
     [MINOR_NAME] ASC,
     [MARKING_PERIOD] ASC,
     [BUILDING] ASC,
     [GRADE] ASC
    )
    )
    ;
    GO

    ALTER TABLE TABLE_A ADD CONSTRAINT FK1
    FOREIGN KEY (APPLICATION, MINOR_NAME)
     REFERENCES TABLE_B (APPLICATION, MINOR_NAME)
    ;

    Friday, March 04, 2011 5:17 PM
  • Read the error message completely and closely.  The message is:

    Server: Msg 1776, Level 16, State 1, Line 2
    There are no primary or candidate keys in the referenced table 'TABLE_B' that match the referencing column list in the foreign key 'FK1'.
    Server: Msg 1750, Level 16, State 1, Line 2
    Could not create constraint. See previous errors.

    Notice the bit about "column list".  What is the order of columns in the primary key of TABLE_B?

    • Marked as answer by Jeff Rix Friday, March 04, 2011 6:53 PM
    Friday, March 04, 2011 6:07 PM
  • Scott,

    Yes the order was different for both. I didn't know that mattered. Thanks Scott!

    Friday, March 04, 2011 6:52 PM