3

I have two parent tables: Treatment and Visit.

Treatment table:

 create table Treatment (
    TreatCode CHAR(6) constraint cTreatCodeNN not null,
    Name VARCHAR2(20),
    constraint cTreatCodePK primary key (TreatCode),
    );

Visit table:

create table Visit (
SlotNum NUMBER(2),
DateVisit DATE,
ActualArrivalTime DATE,
constraint cVisitSlotDatePK primary key (SlotNum, DateVisit)
);

Now I try to create a child table:

create table Visit_Treat (
TreatCode constraint cTreatCodeFK references Treatment(TreatCode),
SlotNum constraint cSlotNumFK references Visit(SlotNum),
DateVisit constraint cDateFK references Visit(DateVisit),
constraint cVisitTreatPK primary key (SlotNum, TreatCode, DateVisit)
);

Everything executes fine till the 3 line. Starting from the 3rd line, i.e. SlotNum constraint ... there is a message: no matching unique or primary key. There was already a similar question, but i did not quite get the logic to apply in my case. I reference each column one by one, and it works for the Treatment table parent. How should i correct reference Visit table parent?

Community
  • 1
  • 1
Buras
  • 3,069
  • 28
  • 79
  • 126

2 Answers2

12
 CONSTRAINT fk_column
 FOREIGN KEY (column1, column2, ... column_n)
 REFERENCES parent_table (column1, column2, ... column_n)

in your case

create table Visit_Treat (
TreatCode CHAR(6) constraint cTreatCodeFK references Treatment(TreatCode),
SlotNum NUMBER(2),
DateVisit DATE,
constraint cVisitTreatPK primary key (SlotNum, TreatCode, DateVisit),
constraint fk_slotnumDatevisit FOREIGN KEY(SlotNum,DateVisit) 
references Visit(SlotNum,DateVisit)
);
chetan
  • 2,876
  • 1
  • 14
  • 15
  • This might or might not be the solution, since we don't know what is actually right & what is wrong in the code, and it doesn't address what (combinations of) declarations should be used in what circumstances. – philipxy Sep 28 '17 at 10:53
3

A foreign key must reference the primary key of the parent table - the entire primary key. In your case, the Visit table's primary key is SlotNum, DateVisit but the foreign key from Visit_Treat only references SlotNum.

You have two good options:

  1. Add a DateVisit column to Visit_Treat and have the foreign key be SlotNum, DateVisit, referencing SlotNum, DateVisit in Visit.

  2. Create a non-business primary key on Visit (for example a column named VisitID of type NUMBER, fed by a sequence), add a VisitID column to Visit_Treat, and make that the foreign key.

And two bad options:

  1. Change the Visit primary key to be only SlotNum so your Visit_Treat foreign key will work. This probably isn't what you want.

  2. Don't use a foreign key. I don't recommend this option. If you're having trouble setting up a foreign key that you know should exist, it generally means a design problem.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • Your first sentence is not correct. An SQL FK references a declared PK or UNIQUE NOT NULL. (More or less a superkey--and not necessarily a minimal/irreducible one, ie a CK.) Since either of those might contain a smaller one or the other, a FK has to reference all of one, but not necessarily all of any particular one, including a PK. You sort of indirectly address this in bad option 1. Although "bad option" seems a misnomer since that is just a case where the design is wrong but the FK is right. – philipxy Sep 28 '17 at 10:51
  • can I see example syntax? I'm stuck on this where I have a composite primary key in table Booking (hotelNo, guestNo, dateFrom) and have a foreign key in table Guest called guestNo that I want referencing guestNo in Booking. – mLstudent33 Mar 02 '20 at 00:53
  • 1
    @mLstudent if the FK really references `Booking` then I don't know how to pull that off. But I'm probably misunderstanding your question. Could you post this as a separate question, and include the table structures? There will be plenty of people who can answer. – Ed Gibbs Mar 02 '20 at 17:33