I have 4 tables called Customer, Pharmacies, Prescriptions, and Refills
Customer table contains customer information like firstname, lastname, phone and email , etc. CustId is the identity seed (primary key).
Pharmacies table contains
PharmacyId pk identity seed,
Prescriptions table contains all drug prescriptions transferred from another pharmacy with:
prescriptionId identity seed PK,
custId FK from Customer table
PharmacyId FK from Pharmacies table
Finally, Refills table contains a drug refills.
RefillId PK identity seed
CustId FK to Customer table
RefillNumber varchar(25) (same as Prescription number from Prescriptions table)
Anytime a customer transfers his or her prescription drug, customer's information is stored in Customer table, telephone number and name of pharmacy is stored in Pharmacy table.
Then latest custid, as well as latest pharmacyId along with prescription numbers are stored in Prescriptions table.
This works fine.
What I am not certain to go about coding is inserting custId into the Refills table without inserting another customer info into the Customer table.
How do I write the INSERT statement so RefillNumber (based on prescription customer is trying to refill), CustID (Customer is attempting the refill) and of course, and RefillID (auto generated) are stored in Refills table?
Your kind assistance is greatly appreciated.