How do I create a foreign key on two columns that are under the primary key constraint?

CREATE TABLE "RESEARCH_DEP"."RESEARCH_TV_COMPANY" 
   (    "CID" NUMBER(38,0) NOT NULL ENABLE, 
    "SOURCE_ID" NUMBER(2,0), 
     CONSTRAINT "PK_RESEARCH_TV_COMPANY_1" PRIMARY KEY ("CID", "SOURCE_ID")
  USING INDEX (CREATE UNIQUE INDEX "RESEARCH_DEP"."IDX_TV_COMPANY_CID_SOURCE_ID" ON "RESEARCH_DEP"."RESEARCH_TV_COMPANY" ("CID", "SOURCE_ID")




CREATE TABLE "RESEARCH_DEP"."RESEARCH_METRICS_PHARMA_AUD" 
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "SOURCE_ID" NUMBER(2,0) NOT NULL ENABLE, 
    "MEDIA_COMPANY_ID" NUMBER(*,0) NOT NULL ENABLE
   )

NEED

ALTER TABLE RESEARCH_DEP.RESEARCH_METRICS_PHARMA_AUD
ADD CONSTRAINT fk_METRICS_PHARMA_AUD_TV_COMPANY_ID
FOREIGN KEY (MEDIA_COMPANY_ID,SOURCE_ID) REFERENCES RESEARCH_DEP.RESEARCH_TV_COMPANY(CID,SOURCE_ID);

ORA-02298: parent keys not found

DO I NEED TO CREATE A COMPOSITION COLUMN: CID,SOURCE_ID? OR create sequence of values two column oracle CID,SOURCE_ID ?

Solution

Your code is more or less OK:

SQL> create table research_tv_company
  2    (cid       number,
  3     source_id number,
  4     --
  5     constraint pk_rtc primary key (cid, source_id)
  6    );

Table created.

SQL> create table research_metrics_pharma_aud
  2    (id               number,
  3     source_id        number,
  4     media_company_id number
  5    );

Table created.

SQL> alter table research_metrics_pharma_aud
  2    add constraint fk_rmpa_rtc
  3    foreign key (media_company_id, source_id)
  4    references research_tv_company (cid, source_id);

Table altered.

SQL>

Note that I

removed double quotes
removed NOT NULL for primary key columns (they can't be NULL anyway)
don't have any indexes created so Oracle will - along with table creation - create a primary key constraint AND unique index to support it
as I'm on 11gXE and it doesn't support identity columns, I removed that from the second table; I'd have to enforce it via a trigger. You don't have to do anything about it, just saying (so that you wouldn't wonder where did it go?)

Error you got:

ORA-02298: parent keys not found

means that there are combinations of (media_company_id, source_id) in the second table that do not exist as pairs of (cid, source_id) in the first table. To illustrate it:

Drop the foreign key constraint first (otherwise, I wouldn't be able to do that):

SQL> alter table research_metrics_pharma_aud drop constraint fk_rmpa_rtc;

Table altered.

Insert a row into the first (master) table:

SQL> insert into research_tv_company (cid, source_id) values (1, 1);

1 row created.

Insert two rows into the second (detail) table; the first combination is valid, the second is not as (2, 2) don't exist in research_tv_company:

SQL> insert into research_metrics_pharma_aud (id, source_id, media_company_id) values (1, 1, 1);

1 row created.

SQL> insert into research_metrics_pharma_aud (id, source_id, media_company_id) values (2, 2, 2);

1 row created.

SQL>

If we try to create a foreign key constraint:

SQL> alter table research_metrics_pharma_aud
  2    add constraint fk_rmpa_rtc
  3    foreign key (media_company_id, source_id)
  4    references research_tv_company (cid, source_id);
  add constraint fk_rmpa_rtc
                 *
ERROR at line 2:
ORA-02298: cannot validate (SCOTT.FK_RMPA_RTC) - parent keys not found


SQL>

See? The same error you got.

What can you do?

insert missing primary key values into the master table, or
delete detail rows that violate the constraint, or
create the constraint, but instruct Oracle not to check whether existing rows are valid or not:
```
SQL> alter table research_metrics_pharma_aud
  2    add constraint fk_rmpa_rtc
  3    foreign key (media_company_id, source_id)
  4    references research_tv_company (cid, source_id)
  5    enable novalidate;

Table altered.

SQL>
```
The enable novalidate means that foreign key constraint will be enabled for any new or modified rows, but existing rows won't be checked. If that option suits you, use it. However, I believe that one of the first two option is better, with the first one - adding missing primary keys - being the best.