[SOLVED] Use CHECK to find if an IRM meetup has been done in the last two month

Use CHECK to find if an IRM meetup has been done in the last two month

I need to create a table for meetup with patientId, doctorId, the meetupdate and the type. And if the type is an IRM, I can't have another meetup if the last one as been less than 2 month ago. I need to do that with a CHECK but it's not working. Here is my code :

CREATE TABLE MeetUp (
    MeetUp ID INT AUTO_INCREMENT PRIMARY KEY,
    PatientID INT,
    DoctorID INT,
    MeetUpDate DATE,
    ExamType VARCHAR(50)
    CHECK (ExamType <> 'IRM' OR (
        NOT EXISTS (
            SELECT 1
            FROM MeetUp AS M2
            WHERE M2.PatientID = MeetUp.PatientID
              AND M2.ExamType = 'IRM'
              AND DATE_ADD(M2.MeetUpDate , INTERVAL 2 MONTH) > MeetUp.MeetUpDate
        )
    )),
    Realise TINYINT(1) NOT NULL,
    CONSTRAINT fk_patient FOREIGN KEY (PatientID) REFERENCES Patients(patient_id),
    CONSTRAINT fk_doctor FOREIGN KEY (DoctorID) REFERENCES Doctors(doctor_id)
);

It is giving me this error "#1146 - table 'meetup' doesn’t exist" I'm using PhpMyAdmin

I'm totally new to database and sql and really don;t know what might be the problem

I tried asking chat GPT, but he told me to use TRIGGERS, but I need to use CHECK

Solution

I need to use CHECK

I'm sorry, but CHECK can't do what you're describing.

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html says:

Subqueries are not permitted.

In fact, a CHECK constraint cannot reference reference other rows in the same table, nor other tables. The expression in a CHECK constraint can only reference other columns in the same row (but not generated columns or the auto-increment column) and a limited subset of deterministic functions.

You need to solve this task in a different way.