0
create table Doctors
(did numeric(3)
,description varchar(20)
,referral varchar(20)
,equipment varchar(10)
, primary key(did));

insert into Doctors values
(101,'physician','fever','medicines'),
(102,'physician','cold','tablet'),
(103,'physician','cough','syrup');

create table HCOReport
(serviceid numeric(3)
,IC numeric(10)
,DC numeric(10)
,referral varchar(20) references Doctors(referral)
,primary key(serviceid)
);

insert into HCOReport values
(201,200,300,'fever'),
(202,300,200,'cold'),
(203,200,283,'cold');

create table Inventory
(itemid numeric(10)
,serviceid numeric(3) references HCOReport(serviceid)
,supplier varchar(20)
,listOfSupplies varchar(30)
);

insert into Inventory values
(101,201,'Blue Ridge','medicines'),
(102,202,'Blue Ridge','stethoscope'),
(103,201,'Blue Ridge','equipment');

create table Patients
(pid numeric(3)
,Isinsurance varchar(3)
,cost numeric(10)
, primary key(pid)
);

insert into Patients values
(501,'yes',1000),
(502,'no',10000),
(504,'no',30000);

I keep getting

there are no primary or candidate keys in the referenced table Doctors that match the referencing column list in the foreign key 'FK__HCOReport__refer__278EDA44'.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Noah Sieff
  • 11
  • The error, regardless of the RDBMS you are using, is telling you the problem; the foreign key you are creating called `FK__HCOReport__refer__278EDA44` is trying to use a column (or columns) that aren't defined as a Unique Value. Foreign Keys can only reference Primary keys or columns with a Unique Constraint/Index on them with the same definition as the foreign key. – Thom A Mar 09 '21 at 17:48
  • The error is pretty self-explanatory... – Andrew Mar 09 '21 at 17:49
  • I also suggest that you give your foreign keys, and other objects, explicit names, both we and you have no idea what key `FK__HCOReport__refer__278EDA44` is. – Thom A Mar 09 '21 at 17:49
  • The `referral` column in the `Doctors` table must have a `UNIQUE` constraint in it, in order to be referenced by a foreign key constraint. Add it, and problem solved. – The Impaler Mar 09 '21 at 17:50

0 Answers0