mysql prevent redundant records with multiple fields

Question

i have a problem here. I have a table of the following schema:

id : int(11) not null primary key
name : varchar(255) not null
status : enum('ACTIVE','DELETED')

Note: The records are 'soft-deleted' and we just flag the status to 'DELETED'.

The issue is that i don't want the a fella to be able to create entry of same name(redundant) via 2 different threads unless the existing record in db is in 'DELETED' state. What's the possible ways to do this?

I cant just unique index the name + status, because if we have an item(with same name) in 'DELETED', and my want-to-be-deleted zone is 'ACTIVE', error will happened as i flag the zone to be 'DELETED'.

ziad-saab · Accepted Answer · 2012-05-28T05:38:31.393

3

How about adding a deleted_at field with your data? Then you can have a unique key on name + status + deleted_at. When status = ACTIVE, there can only be one value for deleted_at, so that will make the uniqueness be on name only.

EDIT for clarity:

CREATE TABLE mytable (
  id int not null primary key,
  name varchar(255) not null,
  status enum('active','delete'),
  deleted_at datetime not null default 0,
  UNIQUE KEY one_active (name, status, deleted_at)
);

EDIT #2: actually, if you make your "check if deleted" code check for deleted_at > 0 instead of checking for status=deleted, then you don't even need the status field.

edited May 28 '12 at 05:38

answered May 28 '12 at 05:31

ziad-saab

19,139
3
36
31

@PaulBellora unfortunately you cannot make it nullable, otherwise the UNIQUE index will not work as expected. It has to be NOT NULL, and the default has to be 0, indicating the record is not soft-deleted. Try it yourself. If you set the deleted_at as nullable, you can create as many records as you want with name='supposedly unique', deleted_at=NULL – ziad-saab May 28 '12 at 05:56
@zi42 - You're absolutely right - I didn't realize [that about unique indexes](http://stackoverflow.com/a/429827/697449) - sorry for doubting! +1 – Paul Bellora May 28 '12 at 06:00
@PaulBellora NP, never be sorry for doubting. It's very healthy to doubt and double-check things you are not sure about. – ziad-saab May 28 '12 at 06:07
Awesome! Btw, we need the status as i donot want other team(e.g. BackEnd) breaks, at least for now. I think your suggestion suit my case. Very thanks for the help! – Solid May 28 '12 at 06:08

score 0 · Answer 2 · answered May 28 '12 at 05:19

0

You should achieve your goal programmatically. Check with your language if name + status ACTIVE exists to restrict INSERT.

answered May 28 '12 at 05:19

Roman Newaza

11,405
11
58
89

mmm tried, we are using hibernate and i cant prevent the redundant record from inserting into db if multiple clients are fast enough to save it. – Solid May 28 '12 at 05:27
I agree. More complicated constraints should be done at the application level, e.g. through Hibernate interceptors or within your DAO. That also seems to suggest that you will need to deal with application-level locking, given that you are constraining on uniqueness. – yclian May 28 '12 at 05:48
Yup, am aware of the proper solution should be at application-level. To make things easier, i think i will go for sql level, at least for now. – Solid May 28 '12 at 06:10

mysql prevent redundant records with multiple fields

2 Answers2