Foreign key must reference a unique key

Foreign key must reference a unique key

I want to create the following tables:

CREATE TABLE tbl_presentation_master
(pres_ref_no varchar(6) Not null,
other fields,
constraint pk_pre
 PRIMARY KEY(pres_ref_no)
);
CREATE TABLE tbl_pres_app_area
(pres_ref_no varchar(6),
app_area_code varchar(2),
Foreign key (pres_ref_no) 
 REFERENCES tbl_presentation_master (pres_ref_no),
constraint prd_tst_pk 
 PRIMARY KEY (app_area_code, pres_ref_no)
);
CREATE TABLE tbl_pres_application
(pres_ref_no varchar(6),
app_area_code varchar(2), 
app_code varchar(2),
Foreign key (app_area_code, pres_ref_no)
 REFERENCES tbl_pres_app_area (app_area_code, pres_ref_no),
constraint prs_app_pk
 PRIMARY KEY (app_area_code, app_code, pres_ref_no)
);
CREATE TABLE tbl_pres_products
(pres_ref_no varchar(6), 
app_code varchar(2),
prd_code varchar(4),
Foreign key (app_code, pres_ref_no)
 REFERENCES tbl_pres_application (app_code, pres_ref_no),
constraint prs_prd_pk
 PRIMARY KEY (app_code, prd_code, pres_ref_no)
);

Now when I run this, the first three tables get created. However, during the creation of fourth table, it gives me error message "no matching unique or primary key for this column-list". What could be wrong?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

That error message is pretty clear. A foreign key must reference a unique key. Primary keys are unique by definition, but you are referencing only two of the three fields in tbl_pres_application. If those two fields are themselves unique, you should declare a separate unique constraint on them.

For More Information


This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top