By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Nurse (
Nurse_ID INT,
NurseFirstName VARCHAR2(50),
NurseSurname VARCHAR2(50),
NurseContact VARCHAR2(20),
NurseLogin VARCHAR2(50),
NursePassword VARCHAR2(50),
PRIMARY KEY (Nurse_ID)
);
CREATE TABLE Appointment (
Appointment_ID INT,
AppointmentDate DATE,
AppointmentTime TIMESTAMP,
AppointmentLocation VARCHAR2(255),
Nurse_ID INT,
Intervention_ID INT,
PRIMARY KEY (Appointment_ID)
);
CREATE TABLE Patient (
Patient_ID INT,
PatientFirstName VARCHAR2(50),
PatientLastName VARCHAR2(50),
PatientDOB DATE,
PatientContact VARCHAR2(20),
PatientAddress VARCHAR2(255),
PatientGP VARCHAR2(50),
PatientAlert VARCHAR2(255),
Appointment_ID INT,
PRIMARY KEY (Patient_ID)
);
CREATE TABLE Nurse_Intervention (
Intervention_ID INT,
InterventionName VARCHAR2(50),
InterventionDescription VARCHAR2(255),
Patient_ID INT,
PRIMARY KEY (Intervention_ID)
);
CREATE TABLE Nurse_Notes (
Note_ID INT,
ContinuationNotes VARCHAR2(255),
TimeStamp TIMESTAMP,
Patient_ID INT,
Nurse_ID INT,
PRIMARY KEY (Note_ID)
);
ALTER TABLE Appointment
ADD CONSTRAINT fk_nurse_id
FOREIGN KEY (Nurse_ID)
REFERENCES Nurse (Nurse_ID);
ALTER TABLE Appointment
ADD CONSTRAINT fk_intervention_id
FOREIGN KEY (Intervention_ID)
REFERENCES Nurse_Intervention (Intervention_ID);
ALTER TABLE Nurse_Notes
ADD CONSTRAINT fk_nurse_id_notes
FOREIGN KEY (Nurse_ID)
REFERENCES Nurse (Nurse_ID);
INSERT INTO patient (patient_id, appointment_id) VALUES (1, 1);
1 rows affected
-- This causes the error
ALTER TABLE PATIENT
ADD CONSTRAINT fk_appointment_id
FOREIGN KEY (Appointment_ID)
REFERENCES Appointment (Appointment_ID);
ORA-02298: cannot validate (FIDDLE_XQRHSFOQSCBMCTKSFJRR.FK_APPOINTMENT_ID) - parent keys not found
TRUNCATE TABLE patient;
-- This causes the error
ALTER TABLE PATIENT
ADD CONSTRAINT fk_appointment_id
FOREIGN KEY (Appointment_ID)
REFERENCES Appointment (Appointment_ID);
ALTER TABLE Nurse_Notes
ADD CONSTRAINT fk_patient_notes
FOREIGN KEY (Patient_ID)
REFERENCES Patient (Patient_ID);