Unique constraint with MYSQL clause

Question

I have a User table in my database.

The user has a field name, company_id and status: boolean, 1- live, 0- deleted.

When a user is deleted, his status is 0.

The combination of a live username in a company must be unique. After the user is deleted, I do not mind that the user was created with the same name for the company.

My question is how to define the uniuqe restriction for the field name, company_id and status = 1 (this is not a uniuqe restriction in these three areas, because I do not mind that the combination name-company_id-0 will be displayed several times in the table).

Thanks,

The yard

Answer 1

Use the NULL value for remote users.
The unique key allows an unlimited number of NULL values.

Update: do not touch the username, NULL in the status field is enough.

Answer 2

What programming language do you use?

Your logical sequence should be as follows:

select * from Table_name where name='' AND company_id = '' AND status = 1 if this return any rows give uniqueness error to the user else create it. 

Answer 3

I would create another column to store the previous name of the remote user and set its real name to NULL when they are deleted (and also set the status to 0).

Then you have a unique restriction on the name and company. NULL will not affect uniqueness (with NULL != NULL ), and you can restore the original username if you want.

So the delete operation looks something like this:

 update users set prev_name = name, name = null, status = 0 where name = 'paxdiablo' and company = 'SmallGreen'; 

Answer 4

Would it be easier if you separate the living and the deleted so that they have their own tinyint / boolean columns?

Answer 5

I would replace the status field with delete_at (datetime). When the user is active, his value will be NULL, but when deleted, it will be set to the current time and date.

Then I will add a unique index to the username and deleted_at fields, which will allow me to delete more than one user with the same username (at least 1 second).