[MySQL] FK Referenced column cannot be a subset of a Unique / Primary Key

[shubham-yb]

In MySQL, you can reference a column which is the part of a Unique Key or a Primary Key for a Foreign Key. This is not allowed in YB / PG.

Workaround: The referenced columns will need to have individual Unique / Primary keys.

Example:

Sample schema on the source (allowed in MySQL):

create table k(id int,id2 int,UNIQUE KEY `uk_k` (`id`,`id2`));

create table h(id int,CONSTRAINT `fk_h` FOREIGN KEY (`id`) REFERENCES `k` (`id`));

Exported Schema:

CREATE TABLE h (
        id bigint
) ;
CREATE TABLE k (
        id bigint,
        id2 bigint
) ;
ALTER TABLE k ADD UNIQUE (id,id2);
ALTER TABLE h ADD CONSTRAINT fk_h FOREIGN KEY (id) REFERENCES k(id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION;

Error during import:

ERROR: there is no unique constraint matching given keys for referenced table "k"

Suggested Changes:

Add individual Unique / Primary keys to the referenced column.