Q
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

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close