Link to home
Start Free TrialLog in
Avatar of Jay Roy
Jay RoyFlag for United States of America

asked on

unique constraint violated --on max() + 1

hi

I am trying to insert into EMPLOYEE table

Insert into employee(ID, NAM, flag)
values ((select max(ID)+1 from employee),'test','Y')

ID is the primary key

but the above insert is failing,
error
unique constraint violated

any idea why since i am doing  'select max(ID)+1'

thanks
Avatar of Sean Stuber
Sean Stuber

you should use sequences instead.


the error is caused because the max can't see uncommitted values.
Sure that ID is your constraint?
SOLUTION
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jay Roy

ASKER

sdstuber, good points

but i just observed something

Insert into employee(ID, NAM, flag)
values ((select max(ID)+1 from employee),'test123','Y') works

but

Insert into employee(ID, NAM, flag)
values ((select max(ID)+1 from employee),'test','Y')

fails

I think the constraint is on the NAM ?
Avatar of Jay Roy

ASKER

is there any sql i can use to check the constraints on a table ?
thx
select * from dba_constraints  (or use all_constraints if you don't have access to the dba view)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial