I need help referencing a non primary key attribute from another table-CodePudding

Im currently doing a project and need some help understanding something. My issue is that I'm trying to "reference"(or something like it) a non-primary attribute from a table. Let's say I have two tables from a database called DoctorsOffice. One table is named Doctor, which goes something like this using SQL:

CREATE TABLE DOCTOR (

Doctor_ID varchar(10) NOT NULL, -- PRIMARY KEY

Last_name varchar(15) NOT NULL,

First_name varchar(20) NOT NULL,

Phone_num varchar(15),

Specialty varchar(12) NOT NULL,

Salary DEC(10, 2),

PRIMARY KEY (Doctor_ID)

);

The second table is Appointments which goes something like this using SQL:

CREATE TABLE APPOINTMENT(

Appointment_num varchar(9) NOT NULL, -- PRIMARY KEY

Test_given varchar(9) NOT NULL,

Patient_ssn char(9) NOT NULL,

Doctor_name varchar(20) NOT NULL,

Doctor_ID varchar(10) NOT NULL,

Date Date,

Room_num char(2) NOT NULL,

PRIMARY KEY (Appointment_num),

FOREIGN KEY (Doctor_name) REFERENCES DOCTOR(First_name), -- this line of code gives me an error

FOREIGN KEY (Doctor_ID) REFERENCES DOCTOR(Doctor_ID)

);

I understand that foreign keys only reference primary keys from other tables. Still, the relational diagram I'm supposed to follow shows an Appointment table attribute Doctor_ID referencing the primary key Doctor_ID and Doctor_name from the Appointment table referencing First_name from the DOCTOR table. First_name isn't a primary key from the DOCTOR table. So, how would I reference an attribute that isn't a primary key?

I also added an image of the relational diagram just in case my description was hard to understand. Notice that there are arrows from the Appointment table attributes Doctor_name and Doctor_ID pointing at the primary key DOCTOR_ID and normal attribute First_name from the DOCTOR table.

CodePudding user response：

I guess that it is not possible, i tried to do in workbench but when you gonna do a reference you need a primary key

CodePudding user response：

If First_name were a key then it could be referenced from another table. For example:

CREATE TABLE DOCTOR (
  Doctor_ID varchar(10) NOT NULL,
  Last_name varchar(15) NOT NULL,
  First_name varchar(20) UNIQUE NOT NULL, -- UNIQUE NOT NULL makes it a key
  Phone_num varchar(15),
  Specialty varchar(12) NOT NULL,
  Salary DEC(10, 2),
  PRIMARY KEY (Doctor_ID)
);

See running example at db<>fiddle.

Note: I don't think the doctor first name is unique. Though the solution above will work from the technical standpoint, I think you need to rethink the database model.