Error 'No Primary or Candidate Keys in the referenced table' when primary table has (Ident-CodePudding

CREATE TABLE #Temp_Actor 
(
    ActorId INT IDENTITY (1,1) NOT NULL
);

INSERT INTO #Temp_Actor DEFAULT VALUES;
INSERT INTO #Temp_Actor DEFAULT VALUES;
INSERT INTO #Temp_Actor DEFAULT VALUES;

CREATE TABLE #Temp_ThirdParty 
(
    ThirdPartyActorId INT NOT NULL,
    CONSTRAINT PK_ThirdPartyActorId 
        PRIMARY KEY (ThirdPartyActorId),
    CONSTRAINT FK_ThirdPartyActorId_ActorId 
        FOREIGN KEY (ThirdPartyActorId) REFERENCES #Temp_Actor (ActorId)
)

I get an error:

There are no primary or candidate keys in the referenced table '#TempActor'

Surely ActorId is the primary key?!

CodePudding user response：

Check it out below.

The missing part is here:

ActorId INT IDENTITY (1,1) PRIMARY KEY)

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS #Temp_Actor;
DROP TABLE IF EXISTS #Temp_ThirdParty;

CREATE TABLE #Temp_Actor (ActorId INT IDENTITY (1,1) PRIMARY KEY);
INSERT INTO #Temp_Actor Default values;
INSERT INTO #Temp_Actor Default values;
INSERT INTO #Temp_Actor Default values;

Create TABLE #Temp_ThirdParty (ThirdPartyActorId INT NOT NULL
, CONSTRAINT PK_ThirdPartyActorId PRIMARY KEY (ThirdPartyActorId)
, CONSTRAINT FK_ThirdPartyActorId_ActorId FOREIGN KEY (ThirdPartyActorId) REFERENCES #Temp_Actor(ActorId));