Sql. How To Reference A Composite Primary Key Oracle?

I have two parent tables: Treatment and Visit. Treatment table: create table Treatment ( TreatCode CHAR(6) constraint cTreatCodeNN not null, Name VARCHAR2(20), constr

Solution 1:

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

in your case

createtable 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)
);

Solution 2:

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.