SQL add constraint

Question

I have an table employees , and I want to add an constraint which makes me able to have ONLY one employee with id_manager null.. I have a sample from my code , but I think it's wrong..

ALTER TABLE employees ADD CONSTRAINT u_t UNIQUE (id_manager);

And this is the error I get.

SQL Error: ORA-02299: (OPREAC_IDD.U_T)
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause:    an alter table validating constraint failed because the table has
       duplicate key values.

sstan · Accepted Answer · 2015-12-25 18:27:45Z

4

You say:

I want to add a constraint which makes me able to have ONLY one employee with id_manager null.

So if I understand this correctly, you're ok with having duplicates in the id_manager column. The only thing you're trying to restrict is making sure that no more than one row can have null as the value for id_manager.

If that's the case, you'll want to create a unique index with an expression so that only null values are considered and validated:

create unique index emp_idx
on employees (case when id_manager is null then 1 end)

answered Dec 25, 2015 at 18:27

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL add constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related