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

Can't create foreign key referencing a virtual column

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

          jplindst Jan Lindström (Inactive) created issue -
          jplindst Jan Lindström (Inactive) made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          jplindst Jan Lindström (Inactive) made changes -
          Assignee Jan Lindström [ jplindst ] Marko Mäkelä [ marko ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          alvinr Alvin Richards (Inactive) made changes -
          Description {noformat}
          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;
          {noformat}
          {noformat}
          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;
          {noformat}

          {code}
          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)

          {code}
          jplindst Jan Lindström (Inactive) made changes -
          Labels 10.2-rc
          jplindst Jan Lindström (Inactive) made changes -
          Fix Version/s 10.2.4 [ 22116 ]
          Fix Version/s 10.2 [ 14601 ]
          marko Marko Mäkelä made changes -
          Assignee Marko Mäkelä [ marko ] Jan Lindström [ jplindst ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          jplindst Jan Lindström (Inactive) made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          jplindst Jan Lindström (Inactive) made changes -
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 79290 ] MariaDB v4 [ 151566 ]

          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.