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

0

I'm trying to create this SQL Server Data Script, but when I run the following error message occurs:

  

Msg 1776, Level 16, State 0, Line 161 There are no primary keys or   candidates in the reference table 'tblSupplierInsumo' which   correspond to the list of reference columns in the foreign key   'FK_tblInsumosDaComposicao_tblFocecedorInsumo'.

  

Msg 1750, Level 16, State 1, Line 161 Could not create the   restriction or index. Please refer to the above errors.

NOTE: "FK_tblInsumosDaComposoTopComponentInsumo" is the relationship name of tblComponents.

These errors happen because of the relationship type in the tblComponentsType table I am trying to create. It is a N: N table that tries to create a relationship with a normal table (tblComposicao) and a relationship table N: N (tblInsumeInsume -> which is the relationship of tblInsumo and tblIncometer where its PK attributes are also FK )).

Below the code I'm trying to execute for creating 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 for the help!

    
asked by anonymous 20.01.2018 / 23:40

1 answer

1

In the declaration of constraint FK_tblInsumosDaComposicao_tblFornecedorInsumo it is necessary that the columns are 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)
    
19.02.2018 / 00:01