CODEWITHSUNDEEP
mysqlsqlunique-constraint
Bachas
Bachas

Reputation: 243

MSQL unique constraint check only onward insert records Not already existing records

The table already has duplicates entries. I want to create a unique constraint in MQSL DB without deleting the existing duplicates. If any duplicate entries coming onwards then it will show an error. Given blow queries not working in MYSQL.

ALTER TABLE presence
ADD CONSTRAINT present uniqueness UNIQUE (employee_id,roll_number) where id >10000;

or 
ALTER TABLE presence
ADD CONSTRAINT present uniqueness UNIQUE (employee_id,roll_number) where id <> (343,34534,34534)

Do we have something like that solution in SQL?

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Add an additional column to the table that indicates the existing values.

Set it to NULL for the existing values. And give it a constant value, say 1, for the new rows. Then create a unique index or constraint on this column:

alter table t add constraint unique (employee_id, is_old)

Actually, I realize that you probably don't want duplicates with singleton old values and new values. That is just an issue of setting the value to NULL only for duplicates in the history. So, one row would have a constant value (say 1) in the historical data.

MySQL allows duplicate values on NULL, which is why this works.

Upvotes: 2

Related Questions