Home » SQL & PL/SQL » SQL & PL/SQL » Trying to Use Foreign Key with Single Field in a Multiple Field Primary Key
Trying to Use Foreign Key with Single Field in a Multiple Field Primary Key [message #198538] Tue, 17 October 2006 09:27 Go to next message
kodemonki
Messages: 8
Registered: May 2006
Junior Member
I'm trying to make a foreign key to a field on an index that is *part* of a primary key. Is this possible?

kpi_reports
-----------
report_id
kpi_id
dsp_id
report_name
report_description
primary key(kpi_id, dsp_id, report_id)

kpi_report_sched
----------------
report_id references kpi_reports(report_id)
day_of_month
kpi_grade

The reason it's set up like this is because let's say there are 8 types of reports, but each dsp needs its own version of each report, but all reports of type 1 need to be sent out on the 3rd day of the month. So the report scheduler only needs to know the report type, no deeper information about the dsp.

Thanks!
Re: Trying to Use Foreign Key with Single Field in a Multiple Field Primary Key [message #198543 is a reply to message #198538] Tue, 17 October 2006 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think it's possible to do that, but if you can create a Unique constraint on KPI_REPORTS.REPORT_ID then you can have a foreign key constraint point to that unique constraint instead.

Re: Trying to Use Foreign Key with Single Field in a Multiple Field Primary Key [message #198548 is a reply to message #198543] Tue, 17 October 2006 10:08 Go to previous messageGo to next message
kodemonki
Messages: 8
Registered: May 2006
Junior Member
I can't do that because there will be a number of the same reports that are associated with different dsps. For instance Report 1 will have a different format for DSP1 and DSP2, so there will be two occurences for Report 1.

Thanks though!
Re: Trying to Use Foreign Key with Single Field in a Multiple Field Primary Key [message #198557 is a reply to message #198548] Tue, 17 October 2006 11:39 Go to previous message
kodemonki
Messages: 8
Registered: May 2006
Junior Member
I just made another table called reports_master and set the report_id there to the primary key and so now I can set report_id in kpi_report_sched to a foreign key.
Previous Topic: Cursor limit
Next Topic: -1247467259ORA-12154:TNS:Could not resolved service name
Goto Forum:
  


Current Time: Sat Dec 03 03:50:44 CST 2016

Total time taken to generate the page: 0.09620 seconds