CODEWITHSUNDEEP
mysqlsqldatabase
Sahat Yalkabov
Sahat Yalkabov

Reputation: 33624

SQL Relations, Triggers and Foreign Keys

In relations Doctor(SSN, First_Name, Last_Name), Clinic (ID, Name, Address) and WorksFor(Doctor_SSN,Clinic_ID), add a trigger: when a doctor transfer from one clinic to another clinic, file a report by inserting the doctor’s first and last name as well as the old and new clinic names into a doctor_transfer relation.

I would like someone to explain if the following SQL code is correct, but in addition how do triggers and foreign keys work.

Tables

CREATE TABLE Doctor(
    SSN INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
);

CREATE TABLE Clinic(
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(255),
);

CREATE TABLE WorksFor(
    Doctor_SSN INT REFERENCES Doctor(SSN),
    Clinic_ID INT REFERENCES Clinic(ID)
);

  1. In the code above, I can get a concrete picture of Doctor and Clinic tables. But how does WorksFor relation work? I sort of understand how foreign keys work from my little experience working on Django framework, but other than that I have no clue what happens in the database when you create a foreign key relation. Does MySQL create a whole new table that includes all attributes of both Doctor and Clinic tables for WorksFor relation?

  2. And then there are triggers. Concept is fairly easy to understand. I would only ask if you could verify that the following SQL code matches the task description above. In short, when old clinic's ID is different than new clinic's id, I create a new table that includes Doctor's first name, last name, old clinic name, new clinic name. Again I am not entirely sure how exactly foreign keys work. When I call current.Name will it give me the Clinic's name since Clinic_ID is referencing the Clinic relation? And same deal with Doctor, will the code below work or did I miss something?

Trigger

CREATE TRIGGER DoctorTransfer(
    AFTER UPDATE ON WorksFor
    REFERENCING
        OLD ROW AS current
        NEW ROW AS new
    FOR EACH ROW
    WHEN(current.Clinic_ID <=> new.Clinic_ID)
    INSERT INTO doctor_transfer VALUES
        VALUES(new.First_Name, new.Last_Name, current.Name as Old Clinic,
                         new.Name as New Clinic);

Upvotes: 2

Views: 1644

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

For #1

Does MySQL create a whole new table that includes all attributes of both Doctor and Clinic tables for WorksFor relation?

The MySQL documentation says the following [emphasis mine]

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

So no it just uses indexes since FK's only care about the existance of a the key in the referenced table (Inserts into the child) or the existance of a referrer in the referring table (Delete/update of parents)

For #2 You have some syntax issues there.

Here's a sample trigger from the Create Trigger docs. No REFERENCES keyword. No WHEN It just directly refers to NEW and uses a standard WHERE clause

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

Upvotes: 1

Related Questions