MSSQL There are no primary or candidate keys in the referenced table-CodePudding

I'm creating 3 tables (1 parent and 2 child) and trying to set foreign keys but I'm getting this error:

There are no primary or candidate keys in the referenced table.

CREATE TABLE WEATHER_FORECASTS(
    WEATHER_ID tinyint NOT NULL,
    TOWN_ID char(2) NOT NULL,
    PRIMARY KEY(WEATHER_ID, TOWN_ID)
);

CREATE TABLE WEATHER(
    WEATHER_ID tinyint NOT NULL REFERENCES WEATHER_FORECASTS(WEATHER_ID),
    WEATHER_TYPE varchar(20) NOT NULL
    PRIMARY KEY(WEATHER_ID)
);

CREATE TABLE TOWNS(
    TOWN_ID char(2) NOT NULL REFERENCES WEATHER_FORECASTS(TOWN_ID),
    TOWN_NAME varchar(20) NOT NULL
);

CodePudding user response：

As @Sticky Bit says you have the Foreign Key the wrong way round, you should have two foreign keys in the weather_forecasts table referencing the tables weather & towns:

CREATE TABLE TOWNS(
    TOWN_ID char(2) NOT NULL,
    TOWN_NAME varchar(20) NOT NULL,
    PRIMARY KEY(TOWN_ID)
);

CREATE TABLE WEATHER(
    WEATHER_ID tinyint NOT NULL,
    WEATHER_TYPE varchar(20) NOT NULL
    PRIMARY KEY(WEATHER_ID)
);

CREATE TABLE WEATHER_FORECASTS(
    WEATHER_ID tinyint NOT NULL REFERENCES WEATHER(WEATHER_ID),
    TOWN_ID char(2) NOT NULL REFERENCES TOWNS(TOWN_ID),
    PRIMARY KEY(WEATHER_ID, TOWN_ID)
);

You may want to consider putting Indexes on those two foreign keys in the WEATHER_FORECASTS table to enhance performance.