MySQL – Is It Possible to Create a Foreign Key with a Condition?

foreign keyMySQL

I have a table site that has a field categoryid_root and a table category which has the fields id and parentid I want to create a relationship between:

site.categoryid_root and category.id but with the condition that category.parentid is null is this at all possible in MySQL?

Best Answer

It is not possible create a foreign key with a condition as you like. But you might use an intermediate table to simulate the condition. I call this table category_root. The category_root table will be updated only by triggers on category table. So you can create the foreign key between site table and category_root tables. No users must be have grants to update category_root to prevent inconsistencies. See diagram to see how the tables are related to each other.

diagram

create table category (
  id int auto_increment, parent_id int, name varchar(45),
  primary key (id),
  constraint fk_parent foreign key (parent_id) references category (id) 
  on delete no action on update no action
) engine=innodb;

create table category_root (
  category_id int not null, 
  primary key (category_id),
  constraint fk_category_id foreign key (category_id) references category (id) 
  on delete cascade on update no action
) engine=innodb;

create table site (
  id int auto_increment, category_root_id int not null, name varchar(45),
  primary key (id),
  constraint fk_category_root foreign key (category_root_id) references category_root (category_id) 
  on delete no action on update no action
) engine=innodb;

The trigger for insert is defined so when an new category without parent_id is inserted into category a new row is insert also into category_root table:

delimiter $$
create trigger t_category_ins after insert on category for each row
begin
  if NEW.parent_id is NULL then
    insert into category_root(category_id) values (NEW.id);
  end if;
end
$$
delimiter ;

The trigger for update is a little more complex:

delimiter $$
create trigger t_category_upd after update on category for each row
begin
  set @new_parent_id = NEW.parent_id;
  set @old_parent_id = OLD.parent_id;

  if (@new_parent_id is NULL) then
    if (@old_parent_id is not NULL) then  
      insert into category_root(category_id) values (NEW.id);
    end if;
  else  
    if @old_parent_id is NULL then
      delete from category_root where category_id = NEW.id;
    end if;
  end if;
end
$$
delimiter ;

Let try to insert some values:

insert into category(name) values('root1');
insert into category(name, parent_id) values('a', last_insert_id()), ('b', last_insert_id());
insert into site(category_root_id, name) values (1, "site1");

select * from category;
+----+-----------+-------+
| id | parent_id | name  |
+----+-----------+-------+
|  1 |      NULL | root1 |
|  2 |         1 | a     |
|  3 |         1 | b     |
+----+-----------+-------+

select * from category_root;
+-------------+
| category_id |
+-------------+
|           1 |
+-------------+

select * from site;
+----+------------------+-------+
| id | category_root_id | name  |
+----+------------------+-------+
|  1 |                1 | site1 |
+----+------------------+-------+

If we try to add a site with a category_id with parent_id not null we got an error:

insert into site(category_root_id, name) values (2, "site1");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`site`, CONSTRAINT `fk_category_root` FOREIGN KEY (`category_root_id`)
REFERENCES `category_root` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Related Question