NONVALIDATE constraint for non-orcale DB?-CodePudding

I am using Amazon RDS in my project that I access via MySQL workbench. I've got an already existing table and inside that table is a column that needs to be unique. But there are some existing duplicate values in that same column.

While using ALTER TABLE table_name ADD UNIQUE(column_name), I get an error that there are duplicate entries in that column.

I search the web and found that oracle database has NONVALIDATE constraint state which means that the constraint is checked for new or modified rows, but existing data may violate the constraint.

I am looking for a similar workaround for my use case, any help would be greatly appreciated.

CodePudding user response：

MySQL (and therefore Amazon RDS for MySQL) has no equivalent to the Oracle feature you describe. You cannot add a UNIQUE constraint to a column that has duplicates.

You can add a UNIQUE constraint after you eliminate the duplicates. Either use UPDATE to change duplicate values, or else use DELETE to remove the rows with duplicates.

CodePudding user response：

You can add a new column, with a default value, populate it for existing rows.
We can then create a unique index on 2 columns.
The new column has to be not null, as does the original column because the unique constraint may allow multiple null values.

create table test(id int not null);
insert into test values(1),(2),(2);

SELECT * FROM test;

| id |
| -: |
|  1 |
|  2 |
|  2 |

ALTER TABLE test ADD UNIQUE KEY(id)

Duplicate entry '2' for key 'test.id'

ALTER TABLE test ADD COLUMN other int default 1 not null;

set @oth = 0;
update test set other = @oth := @oth   1;

ALTER TABLE test ADD UNIQUE KEY(id,other);

SELECT * FROM test;

id | other
-: | ----:
 1 |     1
 2 |     2
 2 |     3

insert into test (id) values (1)

Duplicate entry '1-1' for key 'test.id'

db<>fiddle here