Unanswered: SELECT MAX(pk) FROM .... (constraint????)
I have a litte problem with my private key. I use the following statement:
SELECT MAX(person_pk)+1 FROM person;
in a VALUES statement, in a sql String, in a Java Program.
The problem is, when there is not one entry in the table then something works wrong. Is there a way to add maybe a constraint to my expresion saying (the change has to be done in the SQL string):
if person_pk is Null (is it null when there is no entry?) take 0
else take MAX(person_pk)+1
I am sorry, but I didn't post everything I think I know.
I guess I could use:
INSERT INTO person (person_pk,firstname, lastname,picture)
VALUE(nextval('incpkperson'), me, me,...);
What I meant is that I don`t see how it connects between person_pk and incpkperson, because these are two different things.
I already tried
1.) insert a value with nextval -> inserts an entry with 1
2.) insert a value writing it into the db by hand, setting person_pk to 2 manualy -> inserts an entry with 2
3.) insert a value with nextval ->
ERROR: Cannot insert a duplicate key into unique index pkperson
I guess this is not so bad, as I do not insert any person_pk manually, but what if I wanted to. That was actually my question. Sorry for that.