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.

Paradox table structure does not allow nulls as defined by most other database designs, that is, a value that does not match anything, even another null. Where most databases have nulls, Paradox has blanks. Since MySQL does not have blanks, you need to define a value to use as blank, for each data type. - See also http://stackoverflow.com/questions/10228123/how-does-paradox-manage-null-and-empty-values/13282514#13282514 — A. I. Breveleri, Jan 04 '17 at 17:52

score 0 · Answer 1 · edited Jan 02 '17 at 17:30

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.

score 0 · Answer 2 · answered Jan 02 '17 at 16:39

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

How to replace null primary keys in MySql

2 Answers2