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')
    
    );

can you clarify your goal? your goal is still confusing. My initial understanding is you want only employees with "responsable" role to be the only ones that can exist in departmenst.responsable_id. — suguspnk, Feb 26 '22 at 14:47
please check this. https://stackoverflow.com/a/3880853/5835538 I think this is what you're looking for. — suguspnk, Feb 26 '22 at 15:02
@suguspnk, The [docs on CHECK constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html) says they cannot reference stored functions or subqueries. I don't think the solution you linked to can work in MySQL. — Bill Karwin, Feb 26 '22 at 16:27

score 2 · Accepted Answer · answered Feb 26 '22 at 15:09

It sounds to me like you need a view on DEPARTMENTS which shows the responsible employee. So something like:

CREATE OR REPLACE VIEW department_responsable_view AS
  SELECT d.id,
         d.department_name,
         e.id AS id_responsable_employee,
         e.firstName
    FROM departments d
    LEFT OUTER JOIN employes_tbl e
      ON e.departement = d.department_name AND
         e.role = 'responsable'

db<>fiddle here

Thorsten Kettner · Answer 2 · 2022-02-26T15:37:32.343

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.

how can i Create FOREIGN KEY with condition?

2 Answers2