ORA-02267: incompatible with the data type or collation of the referenced column

Question

create table doctor
    (
        name          varchar2(20)
      , d_id          varchar2(20)
      , address       varchar2(50)
      , phone_number  number(10)
      , qualification varchar2(20)
      , gender        varchar2(20)
      , constraint pk_doctor primary key(d_id)
    )
;

create table room
    (
        room_id   varchar2(5)
      , room_type varchar2(20)
      , constraint pk_room primary key(room_id)
    )
;

create table patient
    (
        p_id           varchar2(10)
      , p_name         varchar2(20)
      , p_age          number(3)
      , p_gender       varchar2(10)
      , address        varchar2(50)
      , date_admission date
      , phone_number   number(10)
      , room_id        varchar2(5)
      , constraint pk_patient primary key(p_id)
      , constraint fk_p1 foreign key(room_id) references room
    )
;

create table bill
    (
        bill_no        varchar2(10)
      , bill_date      date
      , p_id           varchar2(10)
      , p_name         varchar2(20)
      , p_age          number(3)
      , p_gender       varchar2(10)
      , date_admission date
      , date_discharge date
      , room_charges   number(10)
      , pathology_fees number(10)
      , d_fees         number(10)
      , miscellaneous  number(10)
      , total_amount   number(10)
      , constraint pk_bill primary key(bill_no)
      , constraint fk_b1 foreign key(p_id) references patient
      , constraint fk_b2 foreign key(p_name) references patient
      , constraint fk_b3 foreign key(p_age) references patient
      , constraint fk_b4 foreign key(p_gender) references patient
      , constraint fk_b5 foreign key(date_admission) references patient
    )
;

Error starting at line : 15 in command -

create table bill
    (
        bill_no        varchar2(10)
      , bill_date      date
      , p_id           varchar2(10)
      , p_name         varchar2(20)
      , p_age          number(3)
      , p_gender       varchar2(10)
      , date_admission date
      , date_discharge date
      , room_charges   number(10)
      , pathology_fees number(10)
      , d_fees         number(10)
      , miscellaneous  number(10)
      , total_amount   number(10)
      , constraint pk_bill primary key(bill_no)
      , constraint fk_b1 foreign key(p_id) references patient
      , constraint fk_b2 foreign key(p_name) references patient
      , constraint fk_b3 foreign key(p_age) references patient
      , constraint fk_b4 foreign key(p_gender) references patient
      , constraint fk_b5 foreign key(date_admission) references patient
    )

Error report -

ORA-02267: column type incompatible with referenced column type

02267. 00000 - "column type incompatible with referenced column type"

*Cause:  The data type or collation of the referencing column was

  incompatible with the data type or collation of the referenced

  column.

*Action:  Select a compatible data type for the referencing column.

  Also, the collation of a character column in a foreign key must

  match the collation of the corresponding column in the primary

  key.

I understand what the error is trying to tell but my datatypes are same in both tables but still, it is showing the error??

Please tell me where I made a mistake. I would be very grateful.

Answer 1

From the documentation:

If you identify only the parent table or view and omit the column name, then the foreign key automatically references the primary key of the parent table or view.

So you're effectively actually doing:

      , constraint fk_b1 foreign key(p_id) references patient
      , constraint fk_b2 foreign key(p_name) references patient(p_id)
      , constraint fk_b3 foreign key(p_age) references patient(p_id)
      , constraint fk_b4 foreign key(p_gender) references patient(p_id)
      , constraint fk_b5 foreign key(date_admission) references patient(p_id)

which obviously isn't what you want, and explains the error you get.

You need to specify the matching non-PK columns:

      , constraint fk_b1 foreign key(p_id) references patient
      , constraint fk_b2 foreign key(p_name) references patient(p_name)
      , constraint fk_b3 foreign key(p_age) references patient(p_age)
      , constraint fk_b4 foreign key(p_gender) references patient(p_gender)
      , constraint fk_b5 foreign key(date_admission) references patient(date_admission)

However, this will not get

ORA-02270: no matching unique or primary key for this column-list

because those four columns aren't suitable targets; none can really be unique, at least safely. And it wouldn't allow data to change - name and gender could, but age will, for example. These may be recording the patient's status on admission though, so then those wouldn't change, other than to correct mistakes.

It doesn't really make sense to duplicate/denormalise the data. You can just have the PK reference and join to the main table as needed to get the other information.

db<>fiddle

Splitting patient into a table to identify an individual (with date of birth rather than age) and a separate table that records each stay for that patient - with room and admission/discharge dates, for example - might make more sense. Only recording the discharge date on the bill seems odd.