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

Question

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

Answer 1

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.