QUESTION: Automatically generating Primary keys !!!
**** Post for FREE via your newsreader at post.usenet.com ****
Hello,
I'm working on some sort of search-engine which is accesable by a browser.
On of the features of the engine is that you can insert new data into it
just by using your browser. For all this I use PHP, my database is in
Postgresql.
My problem is that I should be able to let the Database System (Postgresql)
generate its own primairy keys (from the rest of the data).
eg. I've got a table like this:
workplace_ID, name, street, nr, zipcode, city, country
workplace_ID is the primairy key of that table. Some other tables have a
foreign key refering to it. As a primairy key this ID is of course unique.
My Question is: How can I see which values (of the primairy key) are already
taken, and which is available for new data.
Thanks a lot
STom
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Hello Tom,
use a sequece. A sequence starts with a defined value, e.g. 1 and
increments with every next-value-call.
Example:
base=# CREATE SEQUENCE table_seq INCREMENT 1 MINVALUE 1;
base=# SELECT nextval('table_seq');
nextval
---------
1
(1 row)
base=# SELECT nextval('table_seq');
nextval
---------
2
(1 row)
etc.
Have a closer look to the creation of sequences at:
http://www.de.postgresql.org/users-lounge/docs/7.1/reference/sql-creates
equence.html
By the way you even can define the default-value for a column to be
taken from a sequence. In this case you do not need to specify the
primary key on insert:
CREATE TABLE t_table (
id numeric(10) default nextval('s_id'),
text text,
primary key (id)
);
INSERT INTO t_table (text) VALUES ('abcd');
Hope this helps ...
Best regards, Jens Hartwig
-----------------------------------------------------
T-Systems
Project Manager
debis Systemhaus GEI GmbH
Address: Eichhornstra�e 3, 10785 Berlin
Postal Address: 10785 Berlin
Phone: (004930) 25 54-32 82
Fax: (004930) 25 54-31 87
Mobile: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de
Show quoted text
Hello,
I'm working on some sort of search-engine which is accesable
by a browser.
On of the features of the engine is that you can insert new
data into it
just by using your browser. For all this I use PHP, my database is in
Postgresql.My problem is that I should be able to let the Database
System (Postgresql)
generate its own primairy keys (from the rest of the data).eg. I've got a table like this:
workplace_ID, name, street, nr, zipcode, city, country
workplace_ID is the primairy key of that table. Some other
tables have a
foreign key refering to it. As a primairy key this ID is of
course unique.
My Question is: How can I see which values (of the primairy
key) are already
taken, and which is available for new data.Thanks a lot
STom
Import Notes
Resolved by subject fallback
Tom Janssens wrote:
My problem is that I should be able to let the Database System (Postgresql)
generate its own primairy keys (from the rest of the data).
workplace_ID, name, street, nr, zipcode, city, country
workplace_ID is the primairy key of that table. Some other tables have a
foreign key refering to it. As a primairy key this ID is of course unique.
My Question is: How can I see which values (of the primairy key) are already
taken, and which is available for new data.
Other than with SELECT?
If it's not too late, I'd make workplace_ID into a serial and let
PostgreSQL generate the values for you.
If it is too late, and workplace_id is text you could try appending
nextval(some_seq) to the end of it - that would guarantee uniqueness.
- Richardh