pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDeferral of primary key constraint
2+ messages / 2 participants
[nested] [flat]
* Deferral of primary key constraint
@ 2000-06-02 15:34 Kyle Bateman <kyle@actarg.com>
2000-06-02 19:14 ` Re: Deferral of primary key constraint Jan Wieck <JanWieck@t-online.de>
0 siblings, 1 reply; 2+ messages in thread
From: Kyle Bateman @ 2000-06-02 15:34 UTC (permalink / raw)
To: pgsql-sql; +Cc: wieck@sapserv.debis.de
Is it possible to defer the check on a primary key constraint (or a
check constraint, for that matter). Here is an example that shows why
it would be nice to be able to do so. We have a real-life scenario that
is similar (but much more complex).
drop table btab;
create table btab (
base varchar,
pos int4,
cmt varchar,
primary key (base, pos)
);
insert into btab (base,pos,cmt) values ('aa',1,'The');
insert into btab (base,pos,cmt) values ('aa',2,'quick');
insert into btab (base,pos,cmt) values ('aa',3,'grey');
insert into btab (base,pos,cmt) values ('aa',4,'fox');
insert into btab (base,pos,cmt) values ('bb',3,'dog');
insert into btab (base,pos,cmt) values ('bb',2,'brown');
insert into btab (base,pos,cmt) values ('bb',1,'The');
select * from btab order by base,pos;
begin;
delete from btab where base = 'aa' and pos = 2;
update btab set pos = pos - 1 where pos > 2 and base = 'aa';
commit;
select * from btab order by base,pos;
begin;
update btab set pos = pos + 1 where pos >= 2 and base = 'bb';
insert into btab (base,pos,cmt) values ('bb',2,'slow');
commit;
select * from btab order by base,pos;
The last transaction fails (on my box, anyway) because of the primary
key index.
We would like to be able to do inserts/deletes to a list of records and
still ensure that they are in contiguous sequential order so we want to
renumber higher records if a new record is inserted in the middle. The
sequence is part of the primary key and we want to ensure uniqueness.
Some renumbering will work (by chance) if the records happen to get
adjusted in the right order. But if one of the updates tries to rename
to an already existing record, it fails.
How hard is it to take the deferral mechanism you have for foreign key
references and apply it to the primary key too? It would also be handy
to be able to defer a check constraint.
Attachments:
kyle.vcf (text/x-vcard, 290B) download
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.com
org:Action Target Inc
adr:;;PO Box 636;Provo;UT;84603;US
version:2.1
email;internet:kyle@actiontarget.com
title:President
x-mozilla-cpt:;-15520
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Deferral of primary key constraint
2000-06-02 15:34 Deferral of primary key constraint Kyle Bateman <kyle@actarg.com>
@ 2000-06-02 19:14 ` Jan Wieck <JanWieck@t-online.de>
0 siblings, 0 replies; 2+ messages in thread
From: JanWieck@t-online.de @ 2000-06-02 19:14 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql
Kyle Bateman wrote:
> Is it possible to defer the check on a primary key constraint (or a
> check constraint, for that matter). Here is an example that shows why
> it would be nice to be able to do so. We have a real-life scenario that
> is similar (but much more complex).
Not yet. Only the trigger based foreign key checks are
deferrable in 7.0. We discovered the need for deferrability
of UNIQUE (what is what you want from the PK things) and
other checks. Not sure up to now how we could do it, but a
known problem.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-06-02 19:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-02 15:34 Deferral of primary key constraint Kyle Bateman <kyle@actarg.com>
2000-06-02 19:14 ` Jan Wieck <JanWieck@t-online.de>
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