pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
References to SERIAL
4+ messages / 3 participants
[nested] [flat]

* References to SERIAL
@ 2000-12-30 16:39  Thomas SMETS <tsmets@altern.org>
  0 siblings, 1 reply; 4+ messages in thread

From: Thomas SMETS @ 2000-12-30 16:39 UTC (permalink / raw)
  To: pgsql-sql


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 ===========



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: References to SERIAL
@ 2000-12-30 18:21  Brett W. McCoy <bmccoy@chapelperilous.net>
  parent: Thomas SMETS <tsmets@altern.org>
  0 siblings, 2 replies; 4+ messages in thread

From: Brett W. McCoy @ 2000-12-30 18:21 UTC (permalink / raw)
  To: Thomas SMETS <tsmets@altern.org>; +Cc: pgsql-sql

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?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: References to SERIAL
@ 2000-12-30 19:03  Oliver Elphick <olly@lfix.co.uk>
  parent: Brett W. McCoy <bmccoy@chapelperilous.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Oliver Elphick @ 2000-12-30 19:03 UTC (permalink / raw)
  To: Thomas SMETS <tsmets@altern.org>; +Cc: pgsql-sql

"Brett W. McCoy" wrote:
  >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 ?
 ...
  >
  >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,
 ...

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 turn not away."         
                                      Matthew 5:42 





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: References to SERIAL
@ 2000-12-30 22:43  Thomas SMETS <tsmets@altern.org>
  parent: Brett W. McCoy <bmccoy@chapelperilous.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Thomas SMETS @ 2000-12-30 22:43 UTC (permalink / raw)
  To: Brett W. McCoy <bmccoy@chapelperilous.net>; +Cc: pgsql-sql

 
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 ===========



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-12-30 22:43 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-30 16:39 References to SERIAL Thomas SMETS <tsmets@altern.org>
2000-12-30 18:21 ` Brett W. McCoy <bmccoy@chapelperilous.net>
2000-12-30 19:03   ` Oliver Elphick <olly@lfix.co.uk>
2000-12-30 22:43   ` Thomas SMETS <tsmets@altern.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox