How to replace null primary keys in MySql

Question

I have to migrate an old Paradox database to MySql. The Paradox database contains composite primary keys with null values (composite keys are composed by 3,4 or 5 fields and it could have 1 or 2 fields with a Null value). The problem is null values in pk are not allowed in MySql. It is impossible to replace the null values directly in Paradox (5 millions of lines in some tables), so how could we do ?

Thanks in advance for your solutions.

Answer 1

I think you have two choices in the MySQL DB,

(a) use a PK, which requires to replace null values / ignore such rows

(b) use a UNIQUE-constraint, which still allows null values, instead of a PK.

For option (a),

I'd suggest to disable the primary key constraint and import the data; then do all necessary corrections and reactivate the primary key. If you want to get rid of rows with null values in the potential PK columns, you could also make use of the IGNORE-keyword, which skips rows violating PK-constraints when inserting (cf. primary key and unique index constraints)

Option (b),

should allow to import the data as is. You can then do corrections or leave the data as is.

Answer 2

Assuming nulls in PK means they aren't needed to be unique, they could be any value, so assign an arbitrary value to nulls.

To do this, create a trigger on the MySQL table like this:

delimiter //
create trigger trig_pk_col_1 before insert on mytable
for each row
begin
    set new.pk_col_1 = coalesce(new.pk_col_1, 999);
    set new.pk_col_2 = coalesce(new.pk_col_2, 999);
    -- etc for other
end;//
delimiter ;

I've chosen 999 as the arbitrary non-null value, but you can chose anything.

If the logic needs to be more sophisticated, you can code it as you like.

Also, I used coalesce() for brevity, but you can use the more verbose if instead if you prefer:

if new.pk_col_1 is null then
    set new.pk_col_1 = 999;
end if;
-- etc