How to prevent primary serial primary key from being updated with number not in sequence?

Question

CREATE TABLE u_account (
Jid serial primary key,
score int4
);

The primary key works fine (updates itself) ok when I update it like this;

INSERT INTO u_account ('score') VALUES ('122233344');

However when I insert a value like this;

INSERT INTO u_account VALUES ('122233344');

This updates the primary key;

I don't want the primary key to accept anything other than the number that is supposed to be coming next.

Someone had set it up for me before so that if I put in this code;

INSERT INTO u_account VALUES ('122233344');

it would ignore the primary key and just update score.

Please help.

Answer 1

It looks like you should just reverse the order of the two fields in your table. Then if you INSERT a single column value, it will overwrite the "score" field and use the primary key serial sequence to generate a value for the other column. This example does what I think you want:

CREATE TABLE u_account (
score int4,
Jid serial primary key
);

INSERT INTO u_account VALUES ('122233344');

Answer 2

You could write a trigger that substitutes the next sequence value for the jid column on every insert.

Answer 3

You can use "DEFAULT" to put the correct value in the primary key field, eg:

INSERT INTO u_account VALUES (DEFAULT, '122233344');