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

[icergb]

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.

 

[btherl]

Is there a reason why you can't specify the column name?  SQL is not supposed to be used the way you are trying to use it.

 

If you really want it to work that way you can try creating the table with the score column first and the serial column second.

[icergb]

I guess, but this is the way it was working before.

 

However that still does not help me from accidently placing  a random number into the "Jid" column;

 

A primary key should not accept any arbitrary number.

 

I am trying to tighten up the loose ends.

 

 

 

[PFMaBiSmAd]

Then don't execute an INSERT query without specifying the column(s).

 

INSERT INTO u_account VALUES (); is a short-cut method and you must satisfy the requirements when using that method - the values supplied are applied to the columns in the order that the columns exist in the table (I'll bet that the remainder of the columns that you did not supply values for received the default values defined for them.)

 

"Those who take short-cut through forest often trip and fall down along path" 

 

Computers only do exactly what their code tells them to do, so there is no such thing as -

 

help me from accidentally placing a random number into the "Jid" column;

Your code must do what you want it to do when you want it to do it.

[btherl]

A primary key should not accept any arbitrary number.

 

The postgres serial data type is a bit of a hack, so it doesn't follow all the rules you might expect it to.  It's basically a standard column with a default value of the next number in a sequence.  Apart from that it acts exactly like a normal column.  So you can set it directly and even update it to a new value.  You can also set the "next in sequence" value if you want.

 

 

[icergb]

The same could be said for "on delete" or "on update" or cascade or even user permissions. These can all be handled from PHP.

Why compare columns at all from postgres when it can be done from PHP?

 

Thats the way it worked. It was made by one of those really expensive postgresql editors that had a limited trial

 

 

How to handle it from PHP I do understand.

 

Everyone has always told me never do it by text commands always get a gui editor.

These 2 guys work for the big boys and don't even know each other.

 

But I am trying to understand why and how it did what it did.

 

Because it did dop that

 

As I said

 

 

 

 

[icergb]

It has something to do with some kind of constraint

 

CREATE TABLE u_account (
Jid integer primary key nextval('u_account_jid_seq'),
score int4);

CREATE SEQUENCE u_account_jid_seq;
SELECT SETVAL('u_account_jid_seq', 
(SELECT max(jid) FROM u_account));

 

This is not quite right because I am not really worried about the max value. I can use a foreign key to match any other tables I want to compare it with.

I need a "constraint" that will not allow the primary key to be tampered with.

 

this way it will always update itself.

 

I can understand using DEFAULT as an update option for the primary key. but I cannot understand using an arbitrary or random number. If my primary key is on 5 I want it to kick out an error if the number is  entered for the primary key is 3990

 

However, if several people are looking at the database at the same time I understand that the primary key may skip a few numbers that fall into order.

 

 

 

[icergb]

Here is a comparison of the table that worked and the table that doesn't work properly;

 

Old TABLE
                                      Table "public.u_account"
  Column   |          Type          |                            Modifiers      
-----------+------------------------+------------------------------------------------------------------
jid | integer                | not null default nextval('u_account_jid_seq'::regclass)
unique_id | character varying(255) |
name      | character varying(255) |
rank      | character varying(255) |
score     | integer                |
Indexes:
    "u_account_pkey" PRIMARY KEY, btree (jid)

NEW TABLE
        Table "public.u_account"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
jid | integer                | not null
unique_id | character varying(255) |
name      | character varying(255) |
rank      | character varying(255) |
score     | integer                |
Indexes:
    "u_account_pkey" PRIMARY KEY, btree (jid)

[btherl]

Is your goal to understand the previous implementation or to implement it yourself?

[icergb]

Can't anybody do it on this board?

 

 

[artacus]

Ok, so you're both lazy and rude.

 

If you specify fewer values in an insert statement than you have columns, it will interpret the first value as being for the first column and so on, and then use null for the remaining columns. This is true in all databases, not just pg.

 

You can force it to do what you want by using postgres' rule system. But it is much more work that just typing in the darn column name.

 

http://www.postgresql.org/docs/8.3/interactive/rules.html

 

Also you can do inserts like this:

 

INSERT INTO u_account VALUES (DEFAULT, 1234);

 

[artacus]

CREATE TABLE u_account (
Jid integer primary key nextval('u_account_jid_seq'),
score int4);

 

You have some syntax errors there, do instead:

CREATE TABLE u_account(
  jid  SERIAL PRIMARY KEY,
  score INT);

 

That will create a sequence for you and set the default for the column.

[icergb]

Well thats the first positive lead Iv'e had in days. Thanks I will give it a go when I have more time.

 

After many hours of searching I was ready to give up.

 

Actually I did. I have no more time to waste.

 

I succeed to the claim that it may as well not be done.

 

actually I think if you search google for "primary key prevent updated" my posts will come up as number 1.

 

CREATE TABLE u_account (
p_id serial primary key,
unique_id varchar(255),
name varchar(255),
rank varchar(255),
score int4
);

CREATE TABLE g_ss (
g_id varchar(255) NOT NULL UNIQUE,
b_bs int4,
u_accountp_id int4 REFERENCES u_account(p_id)
);