Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11850

Can't create foreign key referencing a virtual column

    XMLWordPrintable

Details

    Description

      create or replace table a (
        cola int(10) primary key,
        v_cola int(10) as (cola mod 10) virtual,
        p_cola int(10) as (cola mod 10) persistent
      );
       
      create index v_cola on a (v_cola);
      create index p_cola on a (p_cola);
       
      create or replace table b(
      cola int(10),
      v_cola int(10),
      p_cola int(10)
      );
       
      alter table b add constraint `p_cola_fk`
      foreign key (p_cola) references a (p_cola)
      on delete restrict
      on update restrict;
       
      alter table b add constraint `v_cola_fk`
      foreign key (v_cola) references a (v_cola)
      on delete restrict
      on update restrict;
      

      ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")
      MariaDB [test]> show warnings;
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                                                                                                                              |
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Warning |  150 | Alter  table `test`.`b` with foreign key constraint failed. Parse error in '
      foreign key (v_cola) references a (v_cola)
      on delete restrict
      on update restrict' near 'v_cola)
      on delete restrict
      on update restrict'. |
      | Error   | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                                                                   |
      | Warning | 1215 | Cannot add foreign key constraint                                                                                                                                                                                    |
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      3 rows in set (0.00 sec)
      
      

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            jplindst Jan Lindström (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.