--------------------------------------------------------------------------------
-- Primary Keys
--------------------------------------------------------------------------------

ALTER TABLE veterinarian
    ADD PRIMARY KEY (empid);

ALTER TABLE vet_phone
    ADD PRIMARY KEY (empid, sort);

ALTER TABLE appointment
    ADD PRIMARY KEY (datetime, room);

ALTER TABLE pet
    ADD PRIMARY KEY (petid);

ALTER TABLE appt_pet
    ADD PRIMARY KEY (datetime, room, petid);

ALTER TABLE prescription
    ADD PRIMARY KEY (empid, petid, datetime, drugname);

--------------------------------------------------------------------------------
-- Foreign Keys
--------------------------------------------------------------------------------

ALTER TABLE vet_phone
    ADD FOREIGN KEY (empid)
    REFERENCES veterinarian (empid);

ALTER TABLE appointment
    ADD FOREIGN KEY (empid)
    REFERENCES veterinarian (empid);

ALTER TABLE appt_pet
    ADD FOREIGN KEY (datetime, room)
    REFERENCES appointment (datetime, room);

ALTER TABLE appt_pet
    ADD FOREIGN KEY (petid)
    REFERENCES pet (petid);

ALTER TABLE prescription
    ADD FOREIGN KEY (empid)
    REFERENCES veterinarian (empid);

ALTER TABLE prescription
    ADD FOREIGN KEY (petid)
    REFERENCES pet (petid);

--------------------------------------------------------------------------------
-- Indexes
--------------------------------------------------------------------------------

CREATE INDEX ON veterinarian (name);

CREATE INDEX ON pet (name);
