Error: There are no primary or candidate keys in the referenced table

Question

Error: There are no primary or candidate keys in the referenced table

Navigation

#1 by (1 votes)

0

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?

sql sql-server

asked by anonymous 26.10.2017 / 20:12

1 answer

Get position of an element Open field with value set, but can be edited

score 1 · Answer 1

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

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