0

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

  • Specify MySQL version. Show complete CREATE TABLE script for your table (if something in it is exxces we'll skip it by ourself). – Akina Oct 16 at 19:34
  • A UNIQUE index on all the columns? – Rick James Oct 20 at 3:54
1

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

| improve this answer | |
0

Here is an example what to do to alter your table, you simply have to enter in the UNIQUE your own columns

CREATE TABLE table__(customer_id INT ,transaction_date date,income INT)
INSERT INTO table__ VALUES(1,now(),100),(1,now(),101),(1,now(),102),(1,now(),103),
(2,now(),100),(2,now(),101),(2,now(),102)
ALTER TABLE table__ ADD id BIGINT UNSIGNED AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
  ALTER TABLE table__ MODIFY COLUMN id BIGINT FIRST;
ALTER TABLE `table__` ADD UNIQUE `unique_index`(`customer_id`, `transaction_date`, `income`);
SELECT * FROM table__
id | customer_id | transaction_date | income
-: | ----------: | :--------------- | -----:
 1 |           1 | 2020-10-16       |    100
 2 |           1 | 2020-10-16       |    101
 3 |           1 | 2020-10-16       |    102
 4 |           1 | 2020-10-16       |    103
 5 |           2 | 2020-10-16       |    100
 6 |           2 | 2020-10-16       |    101
 7 |           2 | 2020-10-16       |    102
INSERT INTO table__ VALUES(1,1,now(),100)
Duplicate entry '1' for key 'table__.PRIMARY'

db<>fiddle here

| improve this answer | |
  • The solution does not work for verifying duplicate columns with Null values. You will be able to INSERT INTO table__ VALUES(8,null,null,100) ; INSERT INTO table__ VALUES(9,null,null,100) – relima Oct 16 at 19:03
  • that is correct, but you have asked about combined columns that are unique and not single columns, for that you have to add another unique for that column as for your date, so the answer is correct – nbk Oct 16 at 19:15
  • Bedides id is primary key and not included in the combined unique, because so ayou never get dupes – nbk Oct 16 at 19:17
  • the title explicitly says "when there are null values within the columns" and it is not about single columns. I want the database to not allow for two identical rows.. does not matter if they have nulls or not. if they are identical rows, they should not be allowed – relima Oct 16 at 19:18
  • the title says combined key and the table show mulitle identical rows except the date, so as you now know the answer to your question, use a combined or a sngle cpoumn or you can also use a TRIFFER before insert for that, if unique keys are not enough – nbk Oct 16 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.