Обсуждение: How to have a unique primary key on two tables
Hi everybody, I need to have a primary key which has to be unique on two tables. E.g.: CREATE TABLE first ( id serial NOT NULL, testo text, ) CREATE TABLE second ( id serial NOT NULL, testo text, ) When I insert some text on "first", I would like first.id = second.id + 1, and vice versa. A sort of primary key in common for both tables. Any hints? Thank you very much -- Daniel "bodom_lx" Graziotin - http://daniel.graziotin.net - http://daniel.graziotin.net/bodom_lx.asc - GPG public key
When you use serial a kind of macro is performed: in fact an integer field is created, a sequence is created with a name based on the table's name and the nextval of that sequence is used as the default value for the field. Now you have to do these steps "manually".
CREATE SEQUENCE "public"."tbl_all_ID_seq" INCREMENT 1 MINVALUE 1 START 1 CACHE 1;
CREATE TABLE tbl_first (
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
CREATE TABLE tbl_second (
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
CREATE TABLE tbl_second (
id INTEGER DEFAULT nextval('public."tbl_all_ID_seq"'::text) NOT NULL,
testo text
);
>>> "Daniel "bodom_lx" Graziotin" <daniel.graziotin@gmail.com> 2007-11-22 12:01 >>>
Hi everybody,
I need to have a primary key which has to be unique on two tables.
E.g.:
CREATE TABLE first
(
id serial NOT NULL,
testo text,
)
CREATE TABLE second
(
id serial NOT NULL,
testo text,
)
When I insert some text on "first", I would like first.id = second.id
+ 1, and vice versa.
A sort of primary key in common for both tables.
Any hints?
Thank you very much
--
Daniel "bodom_lx" Graziotin
- http://daniel.graziotin.net
- http://daniel.graziotin.net/bodom_lx.asc - GPG public key
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Hi everybody,
I need to have a primary key which has to be unique on two tables.
E.g.:
CREATE TABLE first
(
id serial NOT NULL,
testo text,
)
CREATE TABLE second
(
id serial NOT NULL,
testo text,
)
When I insert some text on "first", I would like first.id = second.id
+ 1, and vice versa.
A sort of primary key in common for both tables.
Any hints?
Thank you very much
--
Daniel "bodom_lx" Graziotin
- http://daniel.graziotin.net
- http://daniel.graziotin.net/bodom_lx.asc - GPG public key
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
am Thu, dem 22.11.2007, um 12:01:59 +0100 mailte Daniel bodom_lx Graziotin folgendes: > Hi everybody, > I need to have a primary key which has to be unique on two tables. > E.g.: > > CREATE TABLE first > ( > id serial NOT NULL, > testo text, > ) > > CREATE TABLE second > ( > id serial NOT NULL, > testo text, > ) > > When I insert some text on "first", I would like first.id = second.id > + 1, and vice versa. > A sort of primary key in common for both tables. > > Any hints? Yes. Create a SEQUENCE and use nextval(sequence) as primary key on both tables. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, 22 Nov 2007 12:11:20 +0100 "Bart Degryse" <Bart.Degryse@indicator.be> wrote: > When you use serial a kind of macro is performed: in fact an integer field is created, a sequence is created with a namebased on the table's name and the nextval of that sequence is used as the default value for the field. Now you have todo these steps "manually". The second part, if you really mean the ID to be like a primary key is to put a constraint on the tables to assure that an ID in one does not exist in the other. That may sound like overkill if the situation can "never" occur but it doesn't hurt to program defensively. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.