Create a Relationship Table (N:N) that relates to a Normal-Table and another Relationship-Table (N:N) in SQL Server 2014 Management Studio

Question

I am trying to create this SQL Server Data Flock Script, but when I run the following error message:

Msg 1776, Level 16, State 0, Line 161 There are no primary keys or candidates in the reference table 'tblForneorInsumo match the list of reference columns in the foreign key 'FK_tblInsumosDaComposicao_tblFornecedorInsumo'.

---

Msg 1750, Level 16, State 1, Line 161 Could not create restriction or index. See previous errors.

Note: "Fk_tblinsumosomposicao_tblfornecedorimo" is the relationship name of tblInsumsDaComposition.

These errors happen because of the type of relationship in the table tblInsumsDaComposition that I’m trying to create. It is an N:N table that tries to create relationship with a normal table (tblComposicao) and a relationship table N:N(tblFornecedorInsume -> which is the relationship of tblInsumo and tblFornecedor where its PK attributes are also FK (Idinsumo and Idfornecedor)).

Below the code I’m trying to run to create these tables:

CREATE TABLE tblInsumo
(
    IDInsumo INT IDENTITY(1,1) NOT NULL,
    Descricao VARCHAR(100) NOT NULL,
    Deletado BIT NOT NULL,

    CONSTRAINT PK_tblInsumo PRIMARY KEY (IDInsumo),
);
GO


CREATE TABLE tblFornecedor
(
    IDFornecedor INT IDENTITY(1,1) NOT NULL,
    NomeFantasia VARCHAR(150) NOT NULL,
    RazaoSocial VARCHAR(150) NOT NULL,
    FisicaJuridica BIT NOT NULL,
    CpfCnpj VARCHAR(14) NOT NULL,
    Deletado BIT NOT NULL,

    CONSTRAINT PK_tblFornecedor PRIMARY KEY (IDFornecedor)
);
GO


CREATE TABLE tblComposicao
(
    IDComposicao INT IDENTITY(1,1) NOT NULL,
    Descricao VARCHAR(150) NOT NULL,
    Total DECIMAL(18,2) NOT NULL,
    Deletado BIT NOT NULL,

    CONSTRAINT PK_tblComposicao PRIMARY KEY (IDComposicao),
);
GO


CREATE TABLE tblFornecedorInsumo
(
    IDInsumo INT NOT NULL,
    IDFornecedor INT NOT NULL,
    PrecoCusto DECIMAL(18,2) NOT NULL,
    MargemLucro DECIMAL(18,2) NOT NULL,
    PrecoVenda DECIMAL(18,2) NOT NULL,
    DataAtualizacao DATE NOT NULL,

    CONSTRAINT PK_tblForneceInsumo PRIMARY KEY (IDFornecedor, IDInsumo),
    CONSTRAINT FK_tblForneceInsumo_tblInsumo FOREIGN KEY(IDInsumo) REFERENCES tblInsumo(IDInsumo),
    CONSTRAINT FK_tblForneceInsumo_tblFornecedor FOREIGN KEY(IDFornecedor) REFERENCES tblFornecedor(IDFornecedor)
);
GO

     ------Tabela que não é criada e gera as mensagens de erro------
CREATE TABLE tblInsumosDaComposicao
(
    IDComposicao INT NOT NULL,
    IDInsumo INT NOT NULL,
    IDFornecedor INT NOT NULL,
    Quantidade DECIMAL(18,2) NOT NULL,
    Total DECIMAL(18,2) NOT NULL,
    Deletado BIT NOT NULL,

    CONSTRAINT PK_tblInsumosDaComposicao PRIMARY KEY (IDComposicao, IDInsumo, IDFornecedor),
    CONSTRAINT FK_tblInsumosDaComposicao_tblComposicao FOREIGN KEY(IDComposicao) REFERENCES tblComposicao(IDComposicao),
    CONSTRAINT FK_tblInsumosDaComposicao_tblFornecedorInsumo FOREIGN KEY(IDInsumo, IDFornecedor) REFERENCES tblFornecedorInsumo(IDInsumo, IDFornecedor)
);
GO

Thanks in advance to Ajuda !!!

Answer 1

In declaring the restriction FK_tblInsumosDaComposicao_tblFornecedorInsumo columns need to be declared in the same order.

Where is

CONSTRAINT FK_tblInsumosDaComposicao_tblFornecedorInsumo 
           FOREIGN KEY(IDInsumo, IDFornecedor) 
           REFERENCES tblFornecedorInsumo(IDInsumo, IDFornecedor)

replace with

CONSTRAINT FK_tblInsumosDaComposicao_tblFornecedorInsumo 
           FOREIGN KEY (IDFornecedor, IDInsumo) 
           REFERENCES tblFornecedorInsumo (IDFornecedor, IDInsumo)