Unique constrain with condition MYSQL

Question

Hi!

I have User table in my DB.

A user has the fields name, company_id and status: boolean, 1- live, 0- deleted.

When a user is deleted, his status is set to 0.

The combination of a live user name in a company should be unique. After a user is deleted, I don't mind that a user should be created with the same name for the company.

My question is how do I define a uniuqe constrain for the fields name, company_id and status=1 (It's not a uniuqe constrain on those three field becuase I don't mind that the combination of name-company_id-0 will appear a few times in the table).

Thanks,

Dvora

Answer 1

Use NULL value for deleted users.
Unique key allows unlimited number of NULL values.

Update: Don't touch user name, NULL in status field is enough.

Answer 2

Which programming language you are using?

your logic shoule 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 a deleted user's previous name and set their real name to NULL when they're deleted (as well as setting the status to 0).

Then have a unique constraint on name and company. NULLs will not affect the uniqueness (since NULL != NULL) and you can still recover the user's original name if desired.

So the delete operation is something like:

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

Answer 4

Would it be easier if you split "live" and "deleted" so they have their own tinyint/boolean columns ?