-1

This is a sample of my code. I'm using sql server 2008 R2

create table Entidade(
    NIF numeric(9,0) primary key,
    nome varchar(250) not null,
    tipoEnt varchar(4) check (tipoEnt in ('CE','EC','CEEC',null))
)

create table Curso(
    cod int primary key identity(1,1),
    descricao text,
    nHoras decimal(5,2),
    NIFEnt numeric(9,0) references Entidade(NIF),
)

create table AccaoFormacao(
    ref int identity(1,1) not null,
    codCr int references Curso(cod) not null,
    dtInicio date,
    dtFim date,
    BIFDR numeric(8,0) references Formador(BI),

    constraint pkAccaoFormacao primary key(ref, codCr),
)

create table AF_FDO(
    codCr int,
    refAf int,
    BI numeric(8,0) foreign key references Formando(BI),

    constraint fkAF_FDO foreign key(codCr, refAf) references AccaoFormacao(codCr, ref),
    constraint pkAF_FDO primary key(codCr, refAf, BI)
)

Everything goes fine until I try to create the table AF_FDO, it says: "Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'AccaoFormacao' that match the referencing column list in the foreign key 'fkAF_FDO'."

I understand the message but can't figure how to fix it since I'm declaring the primary key in the constraint pkAccaoFormacao it makes no sense to me.

The unique constraint in AccaoFormacao(codCr, ref) works but at the same time it doesn't "fit" in what I'm doing here. AccaoFormacao is a weak entity of Curso thus I have a composite primary key in AccaoFormacao.

EDIT1: Ok I figured it out after hours I feels stupid. I changed the constraint fkAF_FDO foreign key(codCr, refAf) references AccaoFormacao(codCr, ref) in AF_FDO to constraint fkAF_FDO foreign key(codCr, refAf) references AccaoFormacao and it worked. It will get the composite primary key of AccaoFormacao as it should, the other way should also work...

Thanks for the help anyway.

user8022070
  • 1
  • 1
  • numeric(9,0) primary key ? That doesn't seem a good choice.... – Mitch Wheat Jul 02 '13 at 05:03
  • That doesn't seem to be the cause of my problem. The reason I'm using a numeric is because I want to limit the number of digits in that key, for example, a credit card number. – user8022070 Jul 02 '13 at 05:05
  • Didn't say it was. I was pointing out that it's not a good design. Take it or leave it. – Mitch Wheat Jul 02 '13 at 05:07
  • Adding to @alejandro-rama 's answer, I Hope this helps https://stackoverflow.com/questions/16085724/what-is-the-difference-between-primary-key-only-and-primary-key-constraint#targetText=So%20primary%20key%20constraint%20means,column%20as%20a%20primary%20key.&targetText=The%20main%20difference%20is%20that,can%20add%20any%20time%20later. – evilpixi Oct 07 '19 at 15:57

1 Answers1

0

There is no PK in AccaoFormacao , take a look at Ref and CodCr , FKS needs to point a PK.

Alejandro Rama
  • 1
  • when I do "constraint pkAccaoFormacao primary key(ref, codCr)" doesn't affect the columns ref and codCr and make them a composite primary key? – user8022070 Jul 02 '13 at 05:18