CODEWITHSUNDEEP
mysqlsqldatabase
ovnia
ovnia

Reputation: 2500

sql unique constrait with a custom value

I have table:

id emp_id company role
1  3      1       0
2  2      1       0
3  1      1       1

I need to restrict adding 2 employees for a company with role = 1. Like unique key for company & role, but only for role = 1;

In other words, INSERT INTO table (emp_id, company, role) (4, 1, 1) must fail.

The only way to do it in my head - use triggers. Are there another way?

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

ONe way to do this in MySQL is to change the role so it contains 1 or NULL rather than or0. A unique index ignoresNULL`s, so you can do:

create unique index idx_table_company_role on table(company, role);

You can then create a view on the table, if you want to see 0 and 1:

create view v_table as
    select t.*, (case when role = 1 then 1 else 0 end) as newrole
    from table;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

Create a filtered index

create unique index [Indexname]
on [Yourtable](company,role)
where [role] = 1

Upvotes: 0

Related Questions