Error 1776, Level 16

Asked

Viewed 119 times

-1

I am creating a bank and this showing this error when I will create the table discipline, where I want to make a Constraint with the table Coordinator.

Message 1776, Level 16, Status 0, Line 56 There are no keys primary or candidate in the 'Coordinator' reference table which match the list of reference columns in the foreign key 'Fk_discipline'. Message 1750, Level 16, State 1, Line 56 Was not can create the constraint or index. See previous errors.

I can’t find where I might be missing.

CREATE DATABASE AC_02;
GO

USE AC_02
GO

CREATE TABLE Usuario(
  ID INT NOT NULL IDENTITY(1,1)
  , Login VARCHAR(50) NOT NULL UNIQUE
  , Senha VARCHAR(50) NOT NULL
  , DtExpiracao DATETIME DEFAULT ('01/01/1900')
  , CONSTRAINT PK_Usuario PRIMARY KEY ( ID )
)
GO

CREATE TABLE Coordenador(
  ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , CONSTRAINT FK_Coordenador FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO


CREATE TABLE Aluno(
  ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , RA VARCHAR(20) NOT NULL
  , Foto VARCHAR(100) NULL
  , CONSTRAINT FK_Aluno FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO

CREATE TABLE Professor(
   ID INT NOT NULL IDENTITY(1,1)
  , id_usuario INT NOT NULL 
  , Nome VARCHAR(100) NOT NULL
  , Email VARCHAR(100) UNIQUE
  , Celular VARCHAR(15) UNIQUE
  , Apelido VARCHAR(50) NOT NULL
  , CONSTRAINT FK_Professor FOREIGN KEY ( id_usuario ) REFERENCES Usuario ( ID )
)
GO


CREATE TABLE Disciplina(
  ID INT NOT NULL IDENTITY(1,1)
  , Nome VARCHAR(100) UNIQUE
  , Data DATETIME DEFAULT(GETDATE())
  , Status VARCHAR(10) DEFAULT ('Aberta') CHECK(Status IN('Aberta', 'Fechada'))
  , PlanoDeEnsino VARCHAR(500) NOT NULL
  , CargaHoraria INT CHECK(CargaHoraria IN ('40', '80'))
  , Competencias VARCHAR(100) NOT NULL
  , Habilidades VARCHAR(100) NOT NULL
  , Ementa VARCHAR(100) NOT NULL
  , ConteudoProgramatico VARCHAR(100) NOT NULL
  , BibliografiaBasica VARCHAR(100) NOT NULL
  , BibliografiaComplementar VARCHAR(100) NOT NULL
  , PercentualPratico INT CHECK(PercentualPratico IN(0, 100))
  , PercentualTeorico INT CHECK(PercentualTeorico IN(0, 100))
  , idCoordenador INT NOT NULL
  , CONSTRAINT FK_Disciplina FOREIGN KEY ( idCoordenador ) REFERENCES Coordenador ( ID )
)
GO
sql-server

  • You have set PRIMARY KEY only in the User table, missed to set in the other tables.

1 answer

1

Every foreign key is referenced to a primary key. You need to define it in your tables. Only the user table has it. All your other tables work because the foreign key is from the Usuarios table, but as the coordinator does not have, discipline does not make the foreign key.

To create the primary key: CONSTRAINT PK_identificacao PRIMARY KEY ( campo )

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.