Creating composite foreign key constraint

Question

I am trying to create a composite foreign key relationship/constraint. All tables are empty. I have this table:

CREATE TABLE [dbo].[ChemSampleValueTest](
    [SampleNumber] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [Value] [numeric](18, 6) NOT NULL,
    [Accuracy] [varchar](50) NULL,
    [ResultGroupID] [int] NOT NULL,
    [QAState] [nvarchar](32) NOT NULL,
     CONSTRAINT [PK_SampleValueTest] PRIMARY KEY CLUSTERED 
    (
        [SampleNumber] ASC,
        [ParameterID] ASC,
        [ResultGroupID] ASC
    )
) ON [PRIMARY]

and this table:

CREATE TABLE [dbo].[ChemSampleValueEventLinkTest](
    [Event] [int] NOT NULL,
    [SampleNumber] [int] NOT NULL,
    [ResultGroupID] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [QAState] [nvarchar](32) NULL
) ON [PRIMARY]

and I want to link them like this:

alter table [ChemSampleValueEventLinkTest] add 
    constraint FK_ChemSampleValueEvent_ChemSampleValue_test
    foreign key ([SampleNumber], [ResultGroupID], [ParameterID]) 
    references ChemSampleValueTest ([SampleNumber], [ResultGroupID], [ParameterID])

As far as I can tell all column types are the same, but it keeps on saying

There are no primary or candidate keys in the referenced table 'ChemSampleValueTest' that match the referencing column list in the foreign key 'FK_ChemSampleValueEvent_ChemSampleValue_test'.

Where am I going wrong?

Solution

It looks like you need to have your FK/references list in the same order they appear in the PK definition.

This should work:

CREATE TABLE [dbo].[ChemSampleValueTest](
    [SampleNumber] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [Value] [numeric](18, 6) NOT NULL,
    [Accuracy] [varchar](50) NULL,
    [ResultGroupID] [int] NOT NULL,
    [QAState] [nvarchar](32) NOT NULL,
     CONSTRAINT [PK_SampleValueTest] PRIMARY KEY CLUSTERED 
    (
        [SampleNumber] ASC,
        [ParameterID] ASC,
        [ResultGroupID] ASC
    )
) ON [PRIMARY]

CREATE TABLE [dbo].[ChemSampleValueEventLinkTest](
    [Event] [int] NOT NULL,
    [SampleNumber] [int] NOT NULL,
    [ResultGroupID] [int] NOT NULL,
    [ParameterID] [int] NOT NULL,
    [QAState] [nvarchar](32) NULL
) ON [PRIMARY]

alter table [ChemSampleValueEventLinkTest] add 
    constraint FK_ChemSampleValueEvent_ChemSampleValue_test
    foreign key ([SampleNumber], [ParameterID], [ResultGroupID]) 
    references ChemSampleValueTest ([SampleNumber], [ParameterID], [ResultGroupID])

Comments

[sennett]

You are correct. Thinking about it, that makes sense. Composite foreign keys. Mind-bending, but starting to make sense. Thanks for your help mate.