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

Foreign key constraint is incorrectly formed

    XMLWordPrintable

Details

    Description

      Can not create the FK between these two tables.
      Works fine in MySQL and other DBMSs but fails on MariaDB.

      create table in_sync_data_source
      (
         ds_oid               integer not null auto_increment,
         dbms_name            varchar(256) character set utf8mb4 not null,
         server_name          varchar(256) character set utf8mb4,
         cluster_id           varchar(256) character set utf8mb4,
         database_name        varchar(520) character set utf8mb4,
         logid                varchar(256) character set utf8mb4,
         owner_oid            int,
         root_rs_oid          int,
         readonly_ind         tinyint not null,
         ctrl_ins_dtm         timestamp(6) not null default current_timestamp(6),
         ctrl_upd_dtm         timestamp(6) not null default current_timestamp(6) on update current_timestamp(6),
         ctrl_usr_id          varchar(256) character set utf8mb4 not null,
         primary key (ds_oid)
      );
       
      create table in_sync_object
      (
         rs_oid               int not null,
         ds_oid               integer not null,
         object_cat           varchar(520) character set utf8mb4 collate utf8mb4_bin,
         object_schem         varchar(256) character set utf8mb4 collate utf8mb4_bin,
         object_name          varchar(256) character set utf8mb4 collate utf8mb4_bin not null,
         object_sig           varchar(512) character set utf8mb4,
         object_subtype       varchar(60),
         object_ignore_ind    tinyint not null default 0,
         ctrl_ins_dtm         timestamp(6) not null default current_timestamp(6),
         ctrl_upd_dtm         timestamp(6) not null default current_timestamp(6) on update current_timestamp(6),
         ctrl_usr_id          varchar(256) character set utf8mb4 not null,
         primary key (rs_oid)
      );
       
      create unique index IX1IN_SYNC_OBJECT on in_sync_object
      (
         ds_oid,
         object_cat,
         object_schem,
         object_name,
         object_sig
      );
      

      -- [10.9.3-MariaDB]Can't create table `in_sync_object` (errno: 150 "Foreign key constraint is incorrectly formed")
      alter table in_sync_object add constraint FK_IST_ISDS_DS_OID foreign key (ds_oid)
            references in_sync_data_source (ds_oid) on delete restrict on update restrict;
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            faridz Farid
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.