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?