pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmulti-table unique index
6+ messages / 3 participants
[nested] [flat]
* multi-table unique index
@ 2003-06-23 13:58 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 6+ messages in thread
From: Markus Bertheau @ 2003-06-23 13:58 UTC (permalink / raw)
To: pgsql-sql
-- Suppose I have several types of foos
create table foo_types (
foo_type_id serial primary key,
foo_name text not null
);
-- And the foos itself:
create table foo (
foo_id serial primary key,
foo_type_id int not null references foo_types,
foo_general_data1 text
);
-- 1st special foo:
create table foo_1 (
foo_1_id serial primary key,
foo_id int not null references foo,
foo_1_data1 int,
foo_1_data2 text
);
-- 2nd special foo:
create table foo_2 (
foo_2_id serial primary key,
foo_id int not null references foo,
foo_2_data1 numeric(8,2),
foo_2_data2 timestamp
);
-- And I have bars that each have zero or more (special) foos.
create table bar (
bar_id serial primary key
);
create table bar_foos (
bar_id int not null references bar,
foo_id int not null references foo
);
-- Now how do I prevent that I can insert a foo_1 and a foo_2 with
-- the same foo_id? Or is the design errorneous itself?
--
Markus Bertheau
Berlin, Berlin, Germany
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-table unique index
@ 2003-06-23 17:32 Michael A Nachbaur <mike@nachbaur.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 6+ messages in thread
From: Michael A Nachbaur @ 2003-06-23 17:32 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY
DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence
"foo_type_id_seq".
This way all the tables share the same sequence.
On Monday 23 June 2003 06:58 am, Markus Bertheau wrote:
> -- Suppose I have several types of foos
>
> create table foo_types (
> foo_type_id serial primary key,
> foo_name text not null
> );
>
> -- And the foos itself:
>
> create table foo (
> foo_id serial primary key,
> foo_type_id int not null references foo_types,
> foo_general_data1 text
> );
>
> -- 1st special foo:
>
> create table foo_1 (
> foo_1_id serial primary key,
> foo_id int not null references foo,
> foo_1_data1 int,
> foo_1_data2 text
> );
>
> -- 2nd special foo:
>
> create table foo_2 (
> foo_2_id serial primary key,
> foo_id int not null references foo,
> foo_2_data1 numeric(8,2),
> foo_2_data2 timestamp
> );
>
> -- And I have bars that each have zero or more (special) foos.
>
> create table bar (
> bar_id serial primary key
> );
>
> create table bar_foos (
> bar_id int not null references bar,
> foo_id int not null references foo
> );
>
> -- Now how do I prevent that I can insert a foo_1 and a foo_2 with
> -- the same foo_id? Or is the design errorneous itself?
>
> --
> Markus Bertheau
> Berlin, Berlin, Germany
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Michael A Nachbaur <mike@nachbaur.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-table unique index
@ 2003-06-23 18:16 Markus Bertheau <twanger@bluetwanger.de>
parent: Michael A Nachbaur <mike@nachbaur.com>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Bertheau @ 2003-06-23 18:16 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql
В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет:
> Instead of using the "serial" datatype, you can set it to "int4 PRIMARY KEY
> DEFAULT nextval(foo_type_id_seq)" and you can manually create the sequence
> "foo_type_id_seq".
>
> This way all the tables share the same sequence.
Yeah, but I want to force this behaviour. so that it cannot happen by
accident when you insert records without relying on the sequence.
--
Markus Bertheau.
Berlin, Berlin.
Germany.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-table unique index
@ 2003-06-23 18:34 Michael A Nachbaur <mike@nachbaur.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 6+ messages in thread
From: Michael A Nachbaur @ 2003-06-23 18:34 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
On Monday 23 June 2003 11:16 am, Markus Bertheau wrote:
> В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет:
> > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY
> > KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the
> > sequence "foo_type_id_seq".
> >
> > This way all the tables share the same sequence.
>
> Yeah, but I want to force this behaviour. so that it cannot happen by
> accident when you insert records without relying on the sequence.
I believe that's what I recommended. IIRC the "serial" datatype is simply a
shortcut to what I listed above. This way, if you do not explicitly specify
an id for your record, it'll pull the default; which retrieves a new values
from the sequence.
If you want to ensure that a value is unique when a record is inserted, even
if the user explicitly specifies an ID values, you can create a unique
trigger on the tables, but this depends on what you want to do.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-table unique index
@ 2003-06-23 19:08 Markus Bertheau <twanger@bluetwanger.de>
parent: Michael A Nachbaur <mike@nachbaur.com>
0 siblings, 0 replies; 6+ messages in thread
From: Markus Bertheau @ 2003-06-23 19:08 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql
В Пнд, 23.06.2003, в 20:34, Michael A Nachbaur пишет:
> On Monday 23 June 2003 11:16 am, Markus Bertheau wrote:
> > В Пнд, 23.06.2003, в 19:32, Michael A Nachbaur пишет:
> > > Instead of using the "serial" datatype, you can set it to "int4 PRIMARY
> > > KEY DEFAULT nextval(foo_type_id_seq)" and you can manually create the
> > > sequence "foo_type_id_seq".
> > >
> > > This way all the tables share the same sequence.
> >
> > Yeah, but I want to force this behaviour. so that it cannot happen by
> > accident when you insert records without relying on the sequence.
> If you want to ensure that a value is unique when a record is inserted, even
> if the user explicitly specifies an ID values, you can create a unique
> trigger on the tables, but this depends on what you want to do.
I believe what you described is what I want to do. What's a unique
trigger, though?
--
Markus Bertheau.
Berlin, Berlin.
Germany.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: multi-table unique index
@ 2003-06-23 19:40 Richard Huxton <dev@archonet.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2003-06-23 19:40 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
On Monday 23 Jun 2003 2:58 pm, Markus Bertheau wrote:
> -- Suppose I have several types of foos
>
> create table foo_types (
> foo_type_id serial primary key,
> foo_name text not null
> );
>
> -- And the foos itself:
>
> create table foo (
> foo_id serial primary key,
> foo_type_id int not null references foo_types,
> foo_general_data1 text
> );
>
> -- 1st special foo:
>
> create table foo_1 (
> foo_1_id serial primary key,
> foo_id int not null references foo,
> foo_1_data1 int,
> foo_1_data2 text
> );
An alternative to Markus' ideas in the other thread - store the type in
foo/foo_1, then have a foreign key over both. The irritating thing is that
you're duplicating the type info unnecessarily.
CREATE TABLE foo (
foo_id serial unique,
foo_type_id int not null references foo_types,
...
PRIMARY KEY (foo_id,foo_type_id)
);
CREATE TABLE foo_1 (
extra_foo int4 not null,
extra_type int4 not null
extra1 text,
PRIMARY KEY (extra_foo, extra_type)
CONSTRAINT link_to_foo FOREIGN KEY (extra_foo,extra_type) REFERENCES foo_core
(foo_id, foo_type_id)
);
--
Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-06-23 19:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-23 13:58 multi-table unique index Markus Bertheau <twanger@bluetwanger.de>
2003-06-23 17:32 ` Michael A Nachbaur <mike@nachbaur.com>
2003-06-23 18:16 ` Markus Bertheau <twanger@bluetwanger.de>
2003-06-23 18:34 ` Michael A Nachbaur <mike@nachbaur.com>
2003-06-23 19:08 ` Markus Bertheau <twanger@bluetwanger.de>
2003-06-23 19:40 ` Richard Huxton <dev@archonet.com>
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