Re: HELP! - How to use combined key as foreign key in another table???

Richard A Creed wrote:
>
> Hi -
>
> New to oracle, and any help greatly appreciated with this combined
> key problem (which is a syntax thing, I think):
>
> I have a table, Presentation -
>
> CREATE TABLE Presentation (
> CourseID number(6) REFERENCES Course(CourseID),
> LocationID number(6) REFERENCES Location(LocationID),
> StartDate date NOT NULL,
> OfferingTypeID number(2) REFERENCES OfferingType(OfferingTypeID),
> PRIMARY KEY (CourseID, LocationID, StartDate)
> );
>
> Works fine. As you can see a Presentation of a course is uniquely
> identifiable by the type of Course (CourseID) it is, where it is to be held
> (LocationID) and its start date (StartDate). No presentation of a course
> of the same type will ever occur at the same location on the same day.
>
> I have another table, AttendeeLine, which relates an individual Attendee
> to a given instance of Presentation. Obviously many attendees can attend
> a presentation, and any single attendee should be able to attend more than
> one presentation; but it is implicit that a single attendee can't attend
> the exact same presentation twice (because there aren't two of him/her).
> Thus an instance of AttendeeLine can be uniquely identified by a combined
> key of:
>
> (AttendeeID, CourseID, LocationID, StartDate)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> where these latter three are a foreign key (PK of Presentation)
>
> Referential integrity is necessary here, because (obviously again) an
> attendee cannot be put on the AttendeeLine to attend a Presentation which
> doesn't exist.
>
> So I've done this:
>
> CREATE TABLE AttendeeLine (
> AttendeeID number(6) REFERENCES Attendee(AttendeeID),
> CourseID number(6),
> LocationID number(6),
> StartDate date,
> CONSTRAINT fred PRIMARY KEY (AttendeeID, CourseID, LocationID, StartDate)
> FOREIGN KEY (CourseID, LocationID, StartDate)
> REFERENCES Presentation
> );
>

try
constraint fred PRIMARY KEY (AttendeeID, CourseID, LocationID, StartDate), constraint fk_present foreign key (CourseID, LocationID, StartDate)

        references Presentation (CourseID, LocationID, StartDate); Received on Fri Nov 22 1996 - 00:00:00 CET