how can i Create FOREIGN KEY with condition?

Question

the thing that i want to achieve here is to get the id's of only the employes that has a role of 'responsable', i've tried the following statement but it's syntax error

/*CREATE TABLE employes_tbl(
        id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        username VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        matricule CHAR(4) NOT NULL UNIQUE,
        role VARCHAR(255) NOT NULL ,
        firstName VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        cin VARCHAR(255) NOT NULL UNIQUE,
        date_em date NOT NULL,
        departement VARCHAR(255) NOT NULL,
        fonction VARCHAR(255) NOT NULL,
        responsable VARCHAR(255) NOT NULL,
        burreaux VARCHAR(255) NOT NULL,
        post VARCHAR(255) NOT NULL ,
        address VARCHAR(255) NOT NULL,
        photo VARCHAR(255) NOT NULL,
        phone_portable VARCHAR(255) NOT NULL UNIQUE,
        phone_fix VARCHAR(255) NOT NULL UNIQUE,
        phone_extenstion VARCHAR(255) NOT NULL UNIQUE
    );
    */
    
    
    CREATE TABLE departments(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        department_name VARCHAR(255) NOT NULL,
        responsable_id INT NOT NULL,
        FOREIGN KEY (responsable_id) REFERENCES employes_tbl(id WHERE role='responsable')
    
    ); 

  

Answer 1

It would be great if that were directly possible as shown by you, but it isn't. One way is to create a table of responsibles. Another is this:

CREATE TABLE employes_tbl(
  id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  ...
  role VARCHAR(255) NOT NULL ,
  ...
  UNIQUE (id, role)
);

CREATE TABLE departments
(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(255) NOT NULL,
  responsable_employee_id INT NOT NULL,
  responsible_role VARCHAR(255) NOT NULL CHECK (responsible_role = 'responsable'),
  FOREIGN KEY (responsable_employee_id, responsible_role)
      REFERENCES employes_tbl(id, role)
);

The departments table has a composite foreign key referencing the combination of employee ID and role (in spite of the ID alone being unique already in the employee table), so it can place a check constraint on the role and thus only link to responsibles.