ADD FOREIGN KEY - Oracle to MariaDB Migration

In Oracle, primary and foreign key columns can have different (compatible) data types with different precision and lengths.

MariaDB requires primary and foreign key columns to have exactly the same data types.

Oracle:

  -- Parent table
  CREATE TABLE parent1 (c1 NUMBER PRIMARY KEY);
 
  -- Child tables
  CREATE TABLE child1 (c1 NUMBER(*,0));
  CREATE TABLE child2 (c1 NUMBER(5,0));
 
  ALTER TABLE child1 ADD FOREIGN KEY (c1) REFERENCES parent1 (c1); 
  /* Table altered. */
 
  ALTER TABLE child2 ADD FOREIGN KEY (c1) REFERENCES parent1 (c1); 
  /* Table altered. */

MariaDB:

  CREATE TABLE parent1 (c1 BIGINT PRIMARY KEY);
 
  CREATE TABLE child1 (c1 DECIMAL(38,0));
  CREATE TABLE child2 (c1 INT);
 
  ALTER TABLE child1 ADD FOREIGN KEY (c1) REFERENCES parent1 (c1); 
  /* ERROR 1005 (HY000): (errno: 150 "Foreign key constraint is incorrectly formed") */
 
  ALTER TABLE child2 ADD FOREIGN KEY (c1) REFERENCES parent1 (c1); 
  /* ERROR 1005 (HY000): (errno: 150 "Foreign key constraint is incorrectly formed") */

You can see that even a INT column cannot reference a BIGINT column.

The tool converts foreign key columns to match the data types of their corresponding primary key columns.

For more information, see Oracle to MariaDB Migration.