Avatar of royjayd
royjayd
 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
Oracle Database

Avatar of undefined
Last Comment
mradovan10

8/22/2022 - Mon
Sean Stuber

you should use sequences instead.


the error is caused because the max can't see uncommitted values.
Qlemo

Sure that ID is your constraint?
SOLUTION
OP_Zaharin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
royjayd

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 ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
royjayd

ASKER
is there any sql i can use to check the constraints on a table ?
thx
Sean Stuber

select * from dba_constraints  (or use all_constraints if you don't have access to the dba view)
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.