3 Replies Latest reply on Mar 6, 2013 7:07 PM by mtefft

    add constraint foreign key reference with PK field

    995269
      I am very puzzled by the error I'm getting.

      I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):

      create table Test_req (
      Req_ID varchar2(4) not null,
      Req_Comment varchar2(50) null
      );

      create table Test_n_cycle (
      Req_ID varchar2(4) not null,
      Test_ID varchar2(3) not null,
      Cycle_cd char(1) not null,
      Cycle_Comment varchar2(150) null
      );


      Then I successfully performed the two alter statements:

      Alter table test_n_cycle
      add constraint
      foreign key (req_id) references test_req (req_id)
      Error report:
      SQL Error: ORA-00902: invalid datatype
      00902. 00000 - "invalid datatype"

      Is this because req_id is also defined in the Primary key definitions on these
      two tables, thus making the field not valid for referential constraint definition?
        • 1. Re: add constraint foreign key reference with PK field
          995269
          *** Correction ****
          I am very puzzled by the error I'm getting.

          I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):

          create table Test_req (
          Req_ID varchar2(4) not null,
          Req_Comment varchar2(50) null
          );

          create table Test_n_cycle (
          Req_ID varchar2(4) not null,
          Test_ID varchar2(3) not null,
          Cycle_cd char(1) not null,
          Cycle_Comment varchar2(150) null
          );


          Then I successfully performed the two alter statements:

          Alter table test_n_cycle
          add constraint test_n_cycle_pk primary key (req_id, test_id);

          Alter table test_req
          add constraint test_req_pk primary key (req_id);


          Then tried this freign key reference statement
          Alter table test_n_cycle
          add constraint
          foreign key (req_id) references test_req (req_id)
          Error report:
          SQL Error: ORA-00902: invalid datatype
          00902. 00000 - "invalid datatype"

          Is this because req_id is also defined in the Primary key definitions on these
          two tables, thus making the field not valid for referential constraint definition?
          • 2. Re: add constraint foreign key reference with PK field
            >
            Is this because req_id is also defined in the Primary key definitions on these
            two tables, thus making the field not valid for referential constraint definition?
            >
            How would anyone know? You didn't post any primary key or index definitions.
            >
            Then I successfully performed the two alter statements:
            >
            You only posted ONE alter statement.
            • 3. Re: add constraint foreign key reference with PK field
              mtefft
              You left out the constraint name. Try:
              Alter table test_n_cycle
              add constraint test_n_cycle_req_fk
              foreign key (req_id) references test_req (req_id);