一对foreignKey引用一对PrimaryKey表

Question

我有两个表

CREATE TABLE [dbo].[Customer]
(
[SourceID] [int] NOT NULL,
[ID_N] [bigint] NOT NULL,
[RegionCode] [varchar](1) NOT NULL,
[NSID] [int] NOT NULL,
CONSTRAINT [Pk_Customer] PRIMARY KEY CLUSTERED 
([RegionCode] ASC,  [ID_N] ASC)
)

CREATE TABLE [dbo].[Order]
(
[Reference][nvarchar](50) NOT NULL,
[SourceID] [int] NOT NULL,
[ID_N] [nvarchar](50) NULL,
[RegionCode] [varchar](1) NULL,
[Customer_ID_N] [bigint] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
([Reference] ASC)
)

我想把(CustomemrID_N,RegionCode)作为对Customer表的外键引用。

Alter table [dbo].[Order]
ADD CONSTRAINT FK_Order_Customer FOREIGN KEY (Customer_ID_N,RegionCode)
REFERENCES[dbo].[Customer](ID_N,RegionCode)

但是我得到这个错误:

被引用的表'dbo '中没有主键或候选键。与外键'FK_Order_Customer'中的引用列列表匹配的Customer'。

Answer 1

你的尝试有很多问题…大多数是排版。

第一个语句:

CREATE TABLE [dbo].[Customer](
[SourceID] [int] NOT NULL,
[ID_N] [bigint] NOT NULL,
[RegionCode] [varchar](1) NOT NULL,
[NSID] [int] NOT NULL,
CONSTRAINT [Pk_Customer] PRIMARY KEY CLUSTERED 
([RegionCode] ASC,  [ID_N] ASC)

语句末尾缺少一个右括号())。

那么下一个语句:

CREATE TABLE [dbo].[Order](
[Reference][nvarchar](50) NOT NULL,
[SourceID] [int] NOT NULL,
[ID_N] [nvarchar](50) NULL,
[RegionCode] [varchar](1) NULL,
[Customer_ID_N] [bigint] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
([Reference] ASC)

这也是无效的，还缺少一个右括号。

你的最后一句话也是无效的:

Alter table [dbo].[Order]
ADD CONSTRAINT FK_Order_CustomerFOREIGN KEY ([Customer_ID_N],RegionCode)
REFERENCES[dbo].[Customer](ID_N,RegionCode)

CONSTRAINT FK_Order_CustomerFOREIGN KEY应为CONSTRAINT FK_Order_Customer FOREIGN KEY;注意FK_Order_Customer和' FOREIGN '之间的空格。

一旦我们解决了这个问题，我们就会得到这样的东西:

CREATE TABLE [dbo].[Customer](
[SourceID] [int] NOT NULL,
[ID_N] [bigint] NOT NULL,
[RegionCode] [varchar](1) NOT NULL,
[NSID] [int] NOT NULL,
CONSTRAINT [Pk_Customer] PRIMARY KEY CLUSTERED 
([RegionCode] ASC,  [ID_N] ASC))
GO
CREATE TABLE [dbo].[Order](
[Reference][nvarchar](50) NOT NULL,
[SourceID] [int] NOT NULL,
[ID_N] [nvarchar](50) NULL,
[RegionCode] [varchar](1) NULL,
[Customer_ID_N] [bigint] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
([Reference] ASC))
GO
Alter table [dbo].[Order]
ADD CONSTRAINT FK_Order_Customer FOREIGN KEY ([Customer_ID_N],RegionCode)
REFERENCES[dbo].[Customer](ID_N,RegionCode)

注意，当你运行它时，你会得到上面的错误。这是因为你的PK被定义为[RegionCode] ASC, [ID_N] ASC而不是ID_N,RegionCode。让我们通过交换外键来解决这个问题。

Alter table [dbo].[Order]
ADD CONSTRAINT FK_Order_Customer FOREIGN KEY (RegionCode,[Customer_ID_N])
REFERENCES[dbo].[Customer](RegionCode, ID_N)

现在它工作了。

解决印刷错误，然后读取错误;它在告诉你问题所在。