Thread: References to SERIAL

Hi,
If i create a "internal pk" buy defining on a table a field SERIAL.
How do I reference this field in the other table to set the field
possible value ?


create table book (
/* This is an internal primary key for the book description */book_pk serial,.... // End of Book def
);

create table books_authors (
??? // I want to reference the book pk & the author pk to be able to
make the X-ref ?
);

tx,

THomas










-- 
Sat Dec 30 15:23:42 CET 2000

Thomas SMETS                        e-mail : tsmets@altern.org
Av. de la Brabançonne 133 / 3       Tel. : +32 (0)2 742. 05. 94.
1030 Bruxelles
======= Quote of the Day =========
"In the long run, every program becomes rococo, and then rubble."
-- Alan Perlis
========= End of Quote ===========

On Sat, 30 Dec 2000, Thomas SMETS wrote:

> If i create a "internal pk" buy defining on a table a field SERIAL.
> How do I reference this field in the other table to set the field
> possible value ?
>
>
> create table book (
> /* This is an internal primary key for the book description */
>     book_pk serial,
>     .... // End of Book def
> );
>
> create table books_authors (
> ??? // I want to reference the book pk & the author pk to be able to
> make the X-ref ?
> );

You mean as a foreign key?  You would do something like

create table books_authors (book integer references book(book_pk)    on delete no action,author integer references
author(author_pk)   on delete no action,...
 
);

This forces integrity between the tables so the only allowable values in
the books_authors table are those values in the referenced fields (foreign
keys).

You will probably want to look up the documentation on contraints and
foreign keys (I believe they are under the CREATE TABLE documentation).

-- Brett                                    http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Did you hear that there's a group of South American Indians that worship
the number zero?

Is nothing sacred?

"Brett W. McCoy" wrote: >On Sat, 30 Dec 2000, Thomas SMETS wrote: > >> If i create a "internal pk" buy defining on a
tablea field SERIAL. >> How do I reference this field in the other table to set the field >> possible value ?... > >You
meanas a foreign key?  You would do something like > >create table books_authors ( >    book integer references
book(book_pk)>        on delete no action,...
 

If you need to know which value was used for the SERIAL field, there are
two ways:

1. Use currval('book_book_pk_seq')  to get the last value used in this
session.


bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select currval('junk_id_seq');currval 
---------      1
(1 row)

2. Use the OID which is returned by a successful INSERT statement to look
up the newly-created row from the table:


bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select * from junk where oid = 1780993 ;id | name 
----+------ 1 | Fred
(1 row)


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Give to him that asketh thee, and from him that would      borrow of thee
turnnot away."                                              Matthew 5:42 
 

 
Yeap,

Tx Brett. The syntaxe you gave means much more to me than the one I got
from the Book "PostresSQL : Introduction & Concept" from Bruce Momjian
seems a bit short while the PostgreSQL user guide from Thomas LOCKHART
has the full theorical description.

I'll probably stick more with the second one in the future.

Thomas, 



> 
> You mean as a foreign key?  You would do something like
> 
> create table books_authors (
>         book integer references book(book_pk)
>                 on delete no action,
>         author integer references author(author_pk)
>                 on delete no action,
>         ...
> );
> 
> This forces integrity between the tables so the only allowable values in
> the books_authors table are those values in the referenced fields (foreign
> keys).
> 
> You will probably want to look up the documentation on contraints and
> foreign keys (I believe they are under the CREATE TABLE documentation).
> 

-- 
Sat Dec 30 15:23:42 CET 2000

Thomas SMETS                        e-mail : tsmets@altern.org
Av. de la Brabançonne 133 / 3       Tel. : +32 (0)2 742. 05. 94.
1030 Bruxelles
======= Quote of the Day =========
"In the long run, every program becomes rococo, and then rubble."
-- Alan Perlis
========= End of Quote ===========