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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.