创建复合外键约束 [英] Creating composite foreign key constraint

问题描述

我试图创建一个复合外键关系/约束。所有表都为空。我有这个表：

  CREATE TABLE [dbo]。 [ChemSampleValueTest]（
 [SampleNumber] [int] NOT NULL，
 [ParameterID] [int] NOT NULL，
 [Value] [numeric]（18，6）NOT NULL，
 [精度] [varchar]（50）NULL，
 [ResultGroupID] [int] NOT NULL，
 [QAState] [nvarchar]（32）NOT NULL，
 CONSTRAINT [PK_SampleValueTest]主键排序
（
 [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] b  

，我想像这样链接：

  alter table [ChemSampleValueEventLinkTest] add 
约束FK_ChemSampleValueEvent_ChemSampleValue_test 
外键（[SampleNumber]，[ResultGroupID]，[ParameterID]）
引用ChemSampleValueTest（[SampleNumber]，[ResultGroupID]，[ParameterID]）
  

告诉所有列类型是否相同，但它一直在说

引用表中没有主键或候选键
'ChemSampleValueTest'，它匹配外键中的引用列列表
'FK_ChemSampleValueEvent_ChemSampleValue_test'。

我在哪里出错？

解决方案

看起来你需要让你的FK /引用列表按照它们在PK定义中出现的顺序。 p>

这应该工作：

  CREATE TABLE [dbo]。[ChemSampleValueTest] （
 [SampleNumber] [int] NOT NULL，
 [ParameterID] [int] NOT NULL，
 [Value] [numeric]（18,6）NOT NULL，
 [精确度] [varchar]（50）NULL，
 [ResultGroupID] [int] NOT NULL，
 [QAState] [nvarchar]（32）NOT NULL，
 CONSTRAINT [PK_SampleValueTest] 
（
 [SampleNumber] ASC，
 [ParameterID] ASC，
 [ResultGroupID] ASC 
）
）ON [PRIMARY] 
 b $ b CREATE TABLE [dbo]。[ChemSampleValueEventLinkTest]（
 [Event] [int] NOT NULL，
 [SampleNumber] [int] NOT NULL，
 [ResultGroupID] NULL，
 [ParameterID] [int] NOT NULL，
 [QAState] [nvarchar]（32）NULL 
）ON [PRIMARY] 
 
 alter table [ChemSampleValueEventLinkTest ] add 
约束FK_ChemSampleValueEvent_ChemSampleValue_test 
外键（[SampleNumber]，[ParameterID]，[ResultGroupID]）
引用ChemSampleValueTest（[SampleNumber]，[ParameterID]，[ResultGroupID]）
  

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?

解决方案

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])

这篇关于创建复合外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！