Skip site navigation (1) Skip section navigation (2)

Re: Question about Indexes...

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question about Indexes...
Date: 2004-01-03 15:26:15
Message-ID: 3FF6DF17.5060504@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
Aarni Ruuhimäki schrieb:

>You can also use a SEQUENCE which is unique and will add a PK constraint to 
>your field.
>  
>
No. A sequence is just a help to generate sequential numbers as default 
values of a field.
Those fields stay fully editable (not like e.g. in MS-Access Autovalues).
It doesn't imply an index or an unique constraint on that field hence no 
PK either.


>Not sure about the date thing because with sequence you would already have a 
>PK for that row ? A bit confused too ...
>  
>
If you use a sequence and don't manipulate the resulting numbers you get 
an collumn that has PK features in the sense of the text book but its 
unprotected.
A collumn that is marked PRIMARY KEY would go further and let the 
DB-System force that no command would violate the PK features.


Try running your code snippet. You get a sequence and a table.
Then run:

INSERT INTO main_cats VALUES ( 42, 1, 'Tom', 2, FALSE, FALSE, NOW());
INSERT INTO main_cats VALUES ( 42, 1, 'Jerry', 2, FALSE, FALSE, NOW());

INSERT INTO main_cats
(
  lang_id, main_cat_name, mc_order_nro, main_cat_show, sub_cats, last_mod
)
VALUES ( 1, 'Donald', 2, FALSE, FALSE, NOW());

Only the last INSERT used the sequence at all.

Now lets see what is in the table:
db_test=# SELECT main_cat_id, main_cat_name, last_mod FROM main_cats;
 main_cat_id | main_cat_name |          last_mod
-------------+---------------+----------------------------
          42 | Tom           | 2004-01-03 15:48:14.417267
          42 | Jerry         | 2004-01-03 15:48:52.191047
           1 | Donald        | 2004-01-03 15:55:13.685495

main_cat_id has double entries 42 and therefore can't have PK or UNIQUE 
constraints.
It's even worse. The sequence doesn't know that 42 already exists, so it 
will later generate another one.


    main_cat_id integer DEFAULT nextval('"main_cats_main_cat_id_seq"'::text) 
or
    main_cat_id serial

just tells PG to count the sequence one up and take this value as default if no value is explicitly given in an insert.

You have to set the constraints explicitely.

  main_cat_id serial PRIMARY KEY

implies that main_cat_id is NOT NULL and UNIQUE.
Though it doesn't catch issues with manually provided or edited values.
You could still enter ids that arent reached by the sequence, yet and you can change ids that were generated by the sequence as long it doesn't violate NOT NULL and UNIQUE.


cheers
Andreas


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2004-01-03 16:52:30
Subject: Re: Question about Indexes...
Previous:From: G. WalshDate: 2004-01-03 12:14:59
Subject: Creating initial data bases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group