Couple foreignKey reference a table with couple PrimaryKey

Question

I have two tables

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

I want to put (CustomemrID_N,RegionCode) as a Foreign Key reference to the Customer table.

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

but i get this error :

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

Answer 1

There are so many problems with your attempt... Most are typographical.

Firstly this statement:

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)

This is missing a right parenthesis ()) at the end of the statement.

Then the next statement:

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)

This too, isn't valid, there is also a missing right parenthesis.

Your last statement is also, not valid:

  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 should be CONSTRAINT FK_Order_Customer FOREIGN KEY; notice the space between FK_Order_Customer and `FOREIGN.

Once we fix that we get yet something like this:

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)

Note, when you run that, you get the error you have above. That's because your PK is defined as [RegionCode] ASC, [ID_N] ASC not ID_N,RegionCode. So let's fix that as swap the foreign key around...

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

And now it works.

Address the typographical errors, then read the error; it was telling you the problem.