Mysql – How to allow only a certain type of foreign key in a table

best practicesconstraintdatabase-designMySQL

I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed.

I thought of 3 methods to enforce this.

  • Create deliverer table in the middle of employee and order tables to make use of foreign key restrictions: But if I choose this method, I might need to make many other tables like administrator, supervisor, guest and so on for other tables. I fear if this makes things harder unnecessarily. This would be something like this:
CREATE TABLE IF NOT EXISTS temp1_employee
(
    id INT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS temp1_deliverer
(
    employee_id INT NOT NULL PRIMARY KEY,
    CONSTRAINT temp_deliverer_temp_employee_id_fk FOREIGN KEY (employee_id) REFERENCES temp1_employee (id)
);
CREATE TABLE IF NOT EXISTS temp1_order
(
    id           INT AUTO_INCREMENT PRIMARY KEY,
    deliverer_id INT NULL,
    CONSTRAINT temp_order_temp_deliverer_employee_id_fk FOREIGN KEY (deliverer_id) REFERENCES temp1_deliverer (employee_id)
);

  • Add type column in employee table and create a trigger on order table to check if the employee.type is "deliverer": I'm worried about this method since I read that triggers should be used carefully or even avoided if possible.

  • Since this is a business logic(is it?), it should be enforced from applications: But I already know those applications will cause a lot of trouble if it's not enforced by DBMS.

Which method should be used? Maybe there is another?

Best Answer

Possible realization.

  1. Main table 

    CREATE TABLE main ( id INT UNSIGNED PRIMARY KEY, 
                    type VARCHAR(16)
                  );

  2. Prepare main table for referencing 

    ALTER TABLE main 
    ADD COLUMN for_reference INT UNSIGNED AS (CASE WHEN type LIKE 'allowed%' THEN id END) STORED,
    ADD UNIQUE key_for_reference (for_reference);

  3. Create referenced table 

    CREATE TABLE slave ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
                     reference INT UNSIGNED,
                     CONSTRAINT fk_to_main FOREIGN KEY (reference) REFERENCES main (for_reference)
                   );

db<>fiddle

PS. Pay attention - the field in main table which is used for referencing primarily (id) must be unique but NOT autoincremented (because the expression of generated column cannot refer to AI). In general this field must be unique but not primary key.

Related Question