• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Implementing an Entiity Relation diagram

I am a student database designer.  I'm trying to link these two tables below: CONTRACT and PRICEPLAN

I want the two tables to be linked together via the 'PlanCode' field.  

The CONTRACT table details customer data and specifies what "plancode" they have.

The PRICEPLAN table details cost information for telephone services

When I try to implement the PRICEPLAN table in Oracle and put 'plancode' as a foreign key for CONTRACT, it says 'no matching unique or primary key for this column-list'

Any suggestions on how I can implement this?

Account#      Phone#      ContractDate      PlanCode
12345      746-5487      2/2/1990      ABC
12346      548-5486      25/2/91      ABC
12347      246-8754      1/4/1988      BCD
12348      587-5487      14/3/89      BCD
12349      364-2154      21/4/94      ABC

PlanCode      ServiceType      Cost
ABC      VoiceCallPk      5
ABC      DataCallPk      4
ABC      MMCallPk      7
ABC      TextMsgPk      2
ABC      MMMsgPk      3
ABC      VoiceCall      4
ABC      DataCall      3
ABC      MMCall      6
ABC      TextMsg      1
ABC      MMMsg      2
BCD      VoiceCallPk      6
BCD      DataCallPk      5
BCD      MMCallPk      8
BCD      TextMsgPk      3
BCD      MMMsgPk      4
BCD      VoiceCall      5
BCD      DataCall      4
BCD      MMCall      7
BCD      TextMsg      2
BCD      MMMsg      3

2 Solutions
>> 'plancode' as a foreign key for CONTRACT
Foreign keys require the master table column to be UNIQUE/ a primary key column.
Here plancode in CONTRACT is none of them.
You cannot implement this constraint here.

Alternative is: You can have another table for placodes. ANd have a foreign key constraint to that table from CONTRACT and PRICEPLAN.
First, PRICEPLAN.PlanCode needs to be a primary key or unique key.  I suspect it is the Primary Key, right?

Second, the foreign key constraint goes on the CONTRACT table and references the Primary Key of PRICEPLAN.

iannarAuthor Commented:
Thanks for  the help guys..I created another table for plancodes and had foreign key constraints from the other tables...works great!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now