You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Jira Link: DB-13439
I have a foreign constraint defined between two tables in MySQL:
createtablefoo(
id bigint(20) unsigned not null auto_increment comment 'foo\n id\n for \n foo',
created_on datetime default null,
primary key(id)
);
createtablebar(
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:
CREATETABLEbar (
id bigserial,
foo_id numeric(20),
PRIMARY KEY (id)
) ;
ALTERSEQUENCE bar_id_seq RESTART WITH 1;
COMMENT ON COLUMN bar.id IS E'bar id';
CREATETABLEfoo (
id bigserial,
created_on timestamp without time zone,
PRIMARY KEY (id)
) ;
ALTERSEQUENCE 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.
The text was updated successfully, but these errors were encountered:
Jira Link: DB-13439
I have a foreign constraint defined between two tables in MySQL:
The resulting schema is:
After converting and applying the schema to YugabyteDB, I tried to manually add the constraint and get an error:
The issue is that the
foo_id bigint(20) unsigned
is being converted tonumeric(20)
while the PK of foo (also abigint(20) unsigned
) is being converted tobigserial
. As there is no equivalent forbigserial
for unsigned types this will create a logical impossibility in migratingbigint(20) unsigned
PKs as they technically could overflow bigserial.The text was updated successfully, but these errors were encountered: