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);
Table DOCTOR created.
Table ROOM created.
Table PATIENT created.
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.