In mysql, 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?


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