Error: There are no Primary or candidate Keys in the referenced table

Question

I am trying to create a new table in an existing DB, however, appears the following message:

There are no Primary or candidate Keys in the referenced table 'parceria_conta_corrente_ao' that match the referencing column list in the Foreign key 'R_795'.

This table already exists in the database and has data:

I’m trying to create the following table:

CREATE TABLE parceria_item_resgate_rateio_aux
( 
    id_parceria_item_resgate_rateio_aux int  NOT NULL IDENTITY,
    dt_conta_corrente    DATETIME  NOT NULL ,
    id_periodo           BIGINT  NOT NULL ,
    id_ao                bigint  NOT NULL ,
    id_gr_cliente        int  NOT NULL ,
    id_cliente           BIGINT  NOT NULL ,
    data_importacao_cli_gr_cli DATETIME  NOT NULL ,
    hp2                  varchar(50)  NOT NULL ,
    hp2_filho            varchar(50)  NOT NULL ,
    valor_nc             decimal(18,2)  NULL ,
    datetime_inclusion   datetime  NOT NULL ,
    status               int  NULL ,
    CONSTRAINT XPKparceria_item_resgate_ PRIMARY KEY  CLUSTERED 
        (id_parceria_item_resgate_rateio_aux ASC, 
        dt_conta_corrente ASC, 
        id_periodo ASC, 
        id_ao ASC, 
        id_gr_cliente ASC, 
        id_cliente ASC, 
        data_importacao_cli_gr_cli ASC, 
        hp2 ASC),
    CONSTRAINT R_795 FOREIGN KEY(dt_conta_corrente, id_periodo, id_ao, id_gr_cliente, id_cliente, data_importacao_cli_gr_cli, hp2) 
        REFERENCES parceria_conta_corrente_ao(dt_conta_corrente, id_periodo, id_ao, id_gr_cliente, id_cliente, data_importacao_cli_gr_cli, hp2)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)
go

Where is the error?

Answer 1

This list of table columns parceria_conta_corrente_ao

dt_conta_corrente, id_periodo, id_ao, id_gr_cliente, id_cliente, data_importacao_cli_gr_cli, hp2

It is not a primary key to parceria_conta_corrente_ao.

By the way, what you are doing can be considered as bad practice because you are trying to tie up a series of columns without a good reason for it.

Try to use only one column to define a primary key (and therefore a foreign key), because the cause of the problem, in a broader sense, is a design flaw in your database.