Home > Ask the SQL Server Experts > Questions & Answers > Foreign key must reference a unique key
Ask The SQL Server Expert: Questions & Answers
EMAIL THIS

Foreign key must reference a unique key

Rudy Limeback EXPERT RESPONSE FROM: Rudy Limeback

Pose a Question
Other SQL Server Categories
Meet all SQL Server Experts
Become an Expert for this site


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


>
QUESTION POSED ON: 04 September 2003

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


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   



RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice



SQL Solutions - SQL Database Design
HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts