Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 last published in September 2003

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.