Mysql – In thesql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns

alter-tableduplicationMySQLprimary-key

In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row).

How can I alter this table so that it has a composite key which enforces that no duplicate rows exist? Also, the table below does not have a primary key. How can I add a primary key to it?

enter image description here

Best Answer

Possible solution: add into the table structure one more column which is generated and concatenates the whole row, then define this column unique.

Of course if you'll realize this directly then this index will be larger than the table body itself, and it will decrease processing speed dramatically. To avoid this select some hash function, which provides the collision probability small enough, and apply it to concatenated row content. For NULLable columns add a byte which depends of the column value is null, and replace the column's NULL with some constant value.

For example, for the table

CREATE TABLE test ( i_column INT, 
                    t_column DATETIME,
                    s_column TEXT );

this may be

ALTER TABLE test
ADD COLUMN check_uniqueness BINARY(128)
           GENERATED ALWAYS AS (SHA2(
                                     CONCAT(
                                            i_column IS NULL,
                                            COALESCE(i_column, 0),
                                            t_column IS NULL,
                                            COALESCE(t_column, '2000-01-01'),
                                            s_column IS NULL,
                                            COALESCE(s_column, '')
                                           ), 
                                     512
                                    )
                               ) VIRTUAL,
ADD UNIQUE INDEX checking_uniqueness (check_uniqueness);

DEMO fiddle

Related Question