[MySQL] Foreign Key fields of bigint unsigned cause "incompatible types: numeric and bigint"

[ssherwood]

I have a foreign constraint defined between two tables in MySQL:

create table foo(
  id bigint(20) unsigned not null auto_increment comment 'foo\n id\n for \n foo',
  created_on datetime default null,
  primary key(id)
);

create table bar(
  id bigint(20) unsigned not null auto_increment comment 'bar id',
  foo_id bigint(20) unsigned default null,
  primary key(id),
  constraint foo_id_fk foreign key(foo_id) references foo(id)
);

The resulting schema is:

CREATE TABLE bar (
	id bigserial,
	foo_id numeric(20),
	PRIMARY KEY (id)
) ;
ALTER SEQUENCE bar_id_seq RESTART WITH 1;
COMMENT ON COLUMN bar.id IS E'bar id';


CREATE TABLE foo (
	id bigserial,
	created_on timestamp without time zone,
	PRIMARY KEY (id)
) ;
ALTER SEQUENCE foo_id_seq RESTART WITH 1;
COMMENT ON COLUMN foo.id IS E'foo
 id
 for 
 foo';

After converting and applying the schema to YugabyteDB, I tried to manually add the constraint and get an error:

test2=# alter table bar add constraint foo_id_fk foreign key(foo_id) references foo(id);
ERROR:  foreign key constraint "foo_id_fk" cannot be implemented
DETAIL:  Key columns "foo_id" and "id" are of incompatible types: numeric and bigint.

The issue is that the foo_id bigint(20) unsigned is being converted to numeric(20) while the PK of foo (also a bigint(20) unsigned) is being converted to bigserial. As there is no equivalent for bigserial for unsigned types this will create a logical impossibility in migrating bigint(20) unsigned PKs as they technically could overflow bigserial.