Table contains no primary or candidate keys that match the referencing column list in the foreign key

Question

This is my first table :

CREATE TABLE [dbo].[County] 
(
    [CountyId] INT         IDENTITY (1, 1) NOT NULL,
    [County]   VARCHAR(50) NOT NULL,
    CONSTRAINT [PK_County] PRIMARY KEY CLUSTERED ([CountyId] ASC)
);

This is my second table :

CREATE TABLE [dbo].[Theatre] 
(
    [TheatreId]   INT         IDENTITY (1, 1) NOT NULL,
    [TheatreName] VARCHAR(50) NOT NULL,
    [CountyId]    INT         NOT NULL,

    CONSTRAINT [PK_Theatre] 
        PRIMARY KEY CLUSTERED ([TheatreId] ASC),
    CONSTRAINT [FK_Theatre_County] 
        FOREIGN KEY ([CountyId]) REFERENCES [dbo].[County] ([CountyId])
);

This is my third table :

CREATE TABLE [dbo].[Movies] 
(
    [CinemaId]    INT         NOT NULL,
    [CategoryId]  INT         NOT NULL IDENTITY(101, 1),
    [CinemaName]  VARCHAR(50) NOT NULL,
    [TheatreId]   INT         NOT NULL,
    [ShowTimings] TIME (7)    NOT NULL,

    CONSTRAINT [PK_Movies] 
        PRIMARY KEY CLUSTERED ([CinemaId], [CategoryId]),
    CONSTRAINT [FK_Movies_Theatre] 
        FOREIGN KEY ([TheatreId]) REFERENCES [dbo].[Theatre] ([TheatreId])
);

This is my last table:

CREATE TABLE [dbo].[Reviews]
(
    [MovieId]          INT         IDENTITY (1, 1) NOT NULL,
    [Name]             VARCHAR(50) NOT NULL,
    [Genres]           VARCHAR(50) NOT NULL,
    [Image]            VARCHAR(50) NOT NULL,
    [ShortDescription] TEXT        NOT NULL,
    [Rating]           VARCHAR(50) NOT NULL,
    [Grade]            VARCHAR(50) NOT NULL,
    [CategoryId]       INT         NOT NULL,

    CONSTRAINT [PK_Reviews] 
        PRIMARY KEY CLUSTERED ([MovieId] ASC), 
    CONSTRAINT [FK_Reviews_Movies] 
        FOREIGN KEY ([CategoryId]) REFERENCES [Movies]([CategoryId]),   
);

I have created a relationship between the tables yet I am getting an error while I am updating the last table:

SQL71516 :: The referenced table '[dbo].[Movies]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

Can any about please tell me what is my mistake that I am doing?

Answer 1

Just like the error says, Movies.CategoryId is not a key, so you can't reference it in a Foreign Key. You need a Categories table that has CategoryId as its primary key. Then both Reviews and Movies can have a Foreign Key referencing Categories.