Foreign key constraint error during SQL Server table creation

Question

I am trying to create a table on SQL Server which has foreign key constraints.I checked all my parent tables and their primary keys match with my new foreign key constraints. Can someone help me out in resolving this issue. My DDL and error message are as below.

DDL

CREATE TABLE I_IPV_LOB_PROG_PROV_MO_METRIC_TRNS(
    AARP_ORG_ID           int               NOT NULL,
    LOB_ID                int               NOT NULL,
    PGM_CAT_ID            int               NOT NULL,
    PGM_ID                int               NOT NULL,
    PROV_ID               int               NOT NULL,
    CAT_OF_MEAS_ID        int               NOT NULL,
    SUBCAT_OF_MEAS_ID     int               NOT NULL,
    MEAS_ID               int               NOT NULL,
    TYPE_OF_METRIC_ID     int               NOT NULL,
    METRIC_VAL_ROLE_ID    int               NOT NULL,
    MO_ID                 int               NOT NULL,
    OPER_TXT              varchar(5)        NULL,
    METRIC_VAL            decimal(19, 2)    NOT NULL,
    LST_UPD_DT            datetime          NOT NULL,
    LOAD_DT               datetime          NULL,
    LST_UPD_USERID        char(20)          NOT NULL,
    CONSTRAINT PK79 PRIMARY KEY NONCLUSTERED (TYPE_OF_METRIC_ID, METRIC_VAL_ROLE_ID, AARP_ORG_ID, LOB_ID, PGM_CAT_ID, PGM_ID, PROV_ID, MEAS_ID, MO_ID, CAT_OF_MEAS_ID, SUBCAT_OF_MEAS_ID), 
    CONSTRAINT RefI_IPV_TYPE_OF_METRIC_TRNS73 FOREIGN KEY (TYPE_OF_METRIC_ID)
    REFERENCES I_IPV_TYPE_OF_METRIC_TRNS(TYPE_OF_METRIC_ID),
    CONSTRAINT RefI_IPV_METRIC_VAL_ROLE_TRNS75 FOREIGN KEY (METRIC_VAL_ROLE_ID)
    REFERENCES I_IPV_METRIC_VAL_ROLE_TRNS(METRIC_VAL_ROLE_ID),
    CONSTRAINT RefI_IPV_LOB_PROG_PROV_MEAS_TRNS345 FOREIGN KEY (AARP_ORG_ID,LOB_ID,PGM_CAT_ID,PGM_ID,PROV_ID,MEAS_ID,CAT_OF_MEAS_ID,SUBCAT_OF_MEAS_ID)
    REFERENCES I_IPV_LOB_PROG_PROV_MEAS_TRNS(AARP_ORG_ID,LOB_ID,PGM_CAT_ID,PGM_ID,PROV_ID,MEAS_ID,CAT_OF_MEAS_ID,SUB_CAT_OF_MEAS_ID)
)
go

Parent Table DDL

CREATE TABLE I_IPV_LOB_PROG_PROV_MEAS_TRNS(
    AARP_ORG_ID          int         NOT NULL,
    LOB_ID               int         NOT NULL,
    PGM_CAT_ID           int         NOT NULL,
    PGM_ID               int         NOT NULL,
    PROV_ID              int         NOT NULL,
    CAT_OF_MEAS_ID       int         NOT NULL,
    SUBCAT_OF_MEAS_ID    int         NOT NULL,
    MEAS_ID              int         NOT NULL,
    LOAD_DT              datetime    NULL,
    LST_UPD_USERID       char(20)    NOT NULL,
    LST_UPD_DT           datetime    NOT NULL,
    CONSTRAINT PK115 PRIMARY KEY NONCLUSTERED (MEAS_ID, PROV_ID, AARP_ORG_ID, LOB_ID, PGM_ID, PGM_CAT_ID, CAT_OF_MEAS_ID, SUBCAT_OF_MEAS_ID), 
    CONSTRAINT RefI_IPV_LOB_PROG_CAT_PROV_TRNS322 FOREIGN KEY (PROV_ID, AARP_ORG_ID, LOB_ID, PGM_ID, PGM_CAT_ID)
    REFERENCES I_IPV_LOB_PROG_CAT_PROV_TRNS(PROV_ID, AARP_ORG_ID, LOB_ID, PGM_ID, PGM_CAT_ID),
    CONSTRAINT RefI_IPV_MEAS_CAT_TRNS342 FOREIGN KEY (MEAS_ID, CAT_OF_MEAS_ID, SUBCAT_OF_MEAS_ID)
    REFERENCES I_IPV_MEAS_CAT_TRNS(MEAS_ID, CAT_OF_MEAS_ID, SUBCAT_OF_MEAS_ID)
)
go

Error Msg

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'I_IPV_LOB_PROG_PROV_MEAS_TRNS' that match the referencing column list in the foreign key 'RefI_IPV_LOB_PROG_PROV_MEAS_TRNS345'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

you should add DDL of the referenced table that is causing the error — Justin Grant
Well, the error really already says it all: your foreign key ...._TRNS345 is using a list of columns that do NOT match any primary key or unique constraint on the target table. Most likely, your list of columns is just off by one or something. Side-note: a PK made up from 8 columns* (!!!!!) is really really really a bad idea and a huge pain to deal with. Can't you use a **single, surrogate column as your PK ?? — marc_s

simon at rcl simon at rcl · Accepted Answer · 2013-12-18T16:59:47

One of the fields in the target table must be its primary key (best) or be very quickly findable via some index using the at least one of nthe fields you've listed. If there are no keys in your list it will have to read the whole table to find the specific row and this is not a good thing to do, so it won't. Create a unique index on the target table containing some/all of the fields you're constraining and it should be OK.

Foreign key constraint error during SQL Server table creation

1 Answers