how can i Create FOREIGN KEY with condition?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.