CODEWITHSUNDEEP
mysqlsqlmariadbforeign-keyscheck-constraints
Tomáš Růžička
Tomáš Růžička

Reputation: 541

How to check a value of a record referenced by foreign key in a constraint in MySQL

I am currently using MariaDB on arch and whatever is in debian buster reps as mysql in "production".

I'm trying to create following schema:

create or replace schema `test`
use `test`;

create table `tags` 
(
    `tag_id`           int           not null auto_increment,
    `tag_display_name` varchar(200)  not null unique,
    `tag_meta`         boolean       not null,

    primary key (`tag_id`)
);

create table `tag_aliases` 
(
    `alias_id` int not null,
    `tag_id`   int not null,

    primary key (`alias_id`, `tag_id`),

    foreign key (`alias_id`) references `tags`   (`tag_id`),
    foreign key (`tag_id`)   references `tags`   (`tag_id`),
    
-- This constraint I am trying to make that doesn't work (some kind of syntax error 1064)

    CONSTRAINT `cnstrnt` check (select `tag_meta` from `tags` where `tag_id`=`alias_id` limit 1)
);

The goal is to have a m:n relation with tags that have tag_meta column true and with any other tags (meta or not).

But I can't find any resource on this since every time I try to google I see foreign key constraint everywhere.

I also tried to make a function but it says that function cannot be used in check clause. (error 1901)

-- The function

DELIMITER //

create or replace function tagIsMeta(id int) returns boolean
begin
    declare res boolean default null;
    select `tag_meta` into res from `tags` where `tag_id` = id limit 1;
    return res;
end //

DELIMITER ;

-- The constraint

create table .... 
(
    ...
    ...

    CONSTRAINT `cnstrnt` check (tagIsMeta(`alias_id`))

);

Upvotes: 0

Views: 554

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

Here's a solution:

create table `tags` (
    `tag_id`           int           not null auto_increment,
    `tag_display_name` varchar(200)  not null unique,
    `tag_meta`         boolean       not null,

    primary key (`tag_id`),
    key (`tag_id`, `tag_meta`)
);

create table `tag_aliases` (
    `alias_id` int not null,
    `tag_id`   int not null,
    `tag_meta` boolean not null default true,

    primary key (`alias_id`),

    foreign key (`alias_id`) references `tags`   (`tag_id`),
    foreign key (`tag_id`, `tag_meta`)   references `tags`   (`tag_id`, `tag_meta`),
    check (`tag_meta` = true)
);

This uses a nonstandard feature of InnoDB, that it allows a foreign key to reference a non-unique index of the parent table. Normally this is something to avoid, because what does it mean for a child row to reference multiple parent rows? But in this case, since tags.tag_id is unique by itself, that won't happen.

The way this works is that the pair of columns in tag_aliases must match the corresponding pair in tags. But we put a check constraint on tag_aliases.tag_meta that it must be true, therefore that foreign key can be satisfied only if it references a row in tags that has tag_meta = true.

Upvotes: 1

Related Questions