Error: There are no Primary or candidate Keys in the referenced table

Question

I’m trying to relate these two tables

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Periodo') AND type in (N'U'))
DROP TABLE Periodo
GO
CREATE TABLE Periodo(
    Numero tinyint NOT NULL,
    SiglaCurso varchar(5) NOT NULL,
    AnoGrade int NOT NULL,
    SemestreGrade char(1) NOT NULL,
CONSTRAINT [PK_Periodo] PRIMARY KEY CLUSTERED
(
    Numero ASC,
    SiglaCurso ASC,
    AnoGrade ASC,
    SemestreGrade ASC
))
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'GradeCurricular') AND type in (N'U'))
DROP TABLE GradeCurricular
GO
CREATE TABLE GradeCurricular(
    SiglaCurso varchar(5) NOT NULL,
    Ano int NOT NULL,
    Semestre char(1) NOT NULL,
CONSTRAINT [PK_GradeCurricular] PRIMARY KEY CLUSTERED
(
    SiglaCurso ASC,
    Ano ASC,
    Semestre ASC
))
GO

ALTER TABLE Periodo
ADD CONSTRAINT FK_PeriodoGrade_Ano
FOREIGN KEY (AnoGrade) REFERENCES GradeCurricular(Ano)
GO

ALTER TABLE Periodo
ADD CONSTRAINT FK_PeriodoGrade_Semestre
FOREIGN KEY (SemestreGrade) REFERENCES GradeCurricular(Semestre)
GO

When trying to create these two foreign keys I get the following message:

Msg 1776, Level 16, State 0, Line 2 There are no Primary or candidate Keys in the referenced table 'Gradecurricular' that match the referencing column list in the Foreign key 'Fk_periodograde_ano'. Msg 1750, Level 16, State 0, Line 2 Could not create Constraint. See Previous errors.

Where is the error?

Answer 1

The primary key of your table GradeCurricular is composed. You can only make strong relationships if the two keys - primary and foreign - have the same format. Therefore, your foreign keys should be composed as well, to point to the three primary key columns (SiglaCurso, Ano and Semestre).

I understand that you must have made up this composite primary key to allow two courses to be in the same year and the same semester, and still ensure some form of identity for each course. You will suffer much less if you use a simpler design, using a self-adjusted number as the primary key. Then you can add a restriction of type unique in the three columns you use today to identify a course.