Error: There are no primary or candidate keys in the referenced table

Question

Error: There are no primary or candidate keys in the referenced table

Navigation

#1 by (2 votes)

0

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

There are no primary or candidate keys in the referenced table 'match_contact_current' 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?

sql sql-server

asked by anonymous 09.03.2015 / 15:04

1 answer

Query MYSQL with predominance of WHERE clause Method terminates its execution unexpectedly when calling another method

score 2 · Accepted Answer

This column relationship of table parceria_conta_corrente_ao

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

Not a primary key of parceria_conta_corrente_ao .

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

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