-1

Prescription table:

Prescription ID (PK)
Appointment ID FK
Quantity
Drug Name
Patient Name
Physician Name

Appointment table:

Appointment ID (PK)
Center(FK)
Patient ID(FK)

Each prescription (ID) is identified by the appointment id followed by a sequence number (e.g., 2003919_1, 2003919_2)

I've already made the relationships but how do I establish the constraint?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3258494
  • 33
  • 4

1 Answers1

0

To create a foreign key constraint on the appointment id field you can do this:

ALTER TABLE prescription 
ADD CONSTRAINT fk_appointment
FOREIGN KEY (appointment_id)
REFERENCES appointment(appointment_id);

Basically means that the appointment ID value on every Prescription record you create must exist in the Appointment table - this is what you wanted to do?

Right - sorry for not getting back to you on this for a while.

I'd put a before insert trigger on the prescription table that basically counts the number of rows that already exist for the appointment ID on the new prescription, adds one, and then uses that as the prescription ID:

CREATE OR REPLACE TRIGGER tr_prescription_appointment_id
BEFORE INSERT ON prescription
FOR EACH ROW
BEGIN


select to_char(:new.appointment_id) || '_' || to_char(count(prescription_id) + 1)
 into :new.prescription_id
 from prescription
 where appointment_id = :new.appointment_id;

EXCEPTION
 WHEN OTHERS THEN
    raise_application_error(-20700,'Error in setting new prescription_id for appointment_id: '  || :new.appointment_id || '. Error Message: '|| sqlerrm);

END tr_prescription_appointment_id;
/

In my test I just created the two tables with the key columns, then inserted an appointment

select * from appointment

APPOINTMENT_ID
--------------
         1
1 row selected.

Then inserted a prescription - you let the trigger populate the prescription_id column.

insert into prescription (appointment_id) values (1);

select * from prescription;
PRESCRIPTION_ID APPOINTMENT_ID
--------------- --------------
1_1                          1
1 row selected.

Do it again for appointment 1

insert into prescription (appointment_id) values (1);

PRESCRIPTION_ID APPOINTMENT_ID
--------------- --------------
1_1                          1
1_2                          1

2 rows selected.

Hope that does what you need.

Oh, and for the error if the appointment ID for the prescription doesn't exist in the appointment table, that's just a straight foreign key constraint

ALTER TABLE prescription 
 ADD CONSTRAINT prescription_appointment_id_fk
FOREIGN KEY (appointment_id) 
REFERENCES appointment (appointment_id)
ENABLE VALIDATE
Trent
  • 86
  • 5
  • Not exactly, I want to check that every prescription id (pk) is composed of an existing appointment id plus an underscore an a set of sequence of numbers. Example: if 3 prescriptions are derived from appointment 1, then there would be three entries on the prescription ID ( 1_1 1_2 1_3 ). I want to trigger an error if anything to the left of the underscore does not correspond a valid appointment ID – user3258494 Oct 10 '14 at 16:25