pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconstraint and ordered value
4+ messages / 3 participants
[nested] [flat]
* constraint and ordered value
@ 2005-12-27 17:52 David Garamond <lists@zara.6.isreserved.com>
0 siblings, 1 reply; 4+ messages in thread
From: David Garamond @ 2005-12-27 17:52 UTC (permalink / raw)
To: pgsql-sql
Is it possible to use only CHECK constraint (and not triggers) to
completely enforce ordered value of a column (colx) in a table? By that
I mean:
1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on;
2. When deleting (or updating), "holes" must not be formed, e.g. if
there are three rows then row with colx=3 must be the first one deleted,
and then colx=2 the second, and so on.
I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and
a CHECK constraint that calls some PL function where the function does a
simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX(colx)+1).
But is it possible to do #2 using only constraints?
Regards,
dave
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: constraint and ordered value
@ 2005-12-29 07:16 Bruno Wolff III <bruno@wolff.to>
parent: David Garamond <lists@zara.6.isreserved.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2005-12-29 07:16 UTC (permalink / raw)
To: David Garamond <lists@zara.6.isreserved.com>; +Cc: pgsql-sql
On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <lists@zara.6.isreserved.com> wrote:
> Is it possible to use only CHECK constraint (and not triggers) to
> completely enforce ordered value of a column (colx) in a table? By that
> I mean:
>
> 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on;
>
> 2. When deleting (or updating), "holes" must not be formed, e.g. if
> there are three rows then row with colx=3 must be the first one deleted,
> and then colx=2 the second, and so on.
>
> I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and
> a CHECK constraint that calls some PL function where the function does a
> simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX(colx)+1).
>
> But is it possible to do #2 using only constraints?
No. A constraint only applies to one row at a time. If you try to work around
this by calling a function that does queries it isn't guarenteed to work.
And if you are thinking of calling a function that does a query, you aren't
looking at saving time over using triggers.
Also, if you are going to have concurrent updates, you are going to need to
do table locking to make this work.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: constraint and ordered value
@ 2005-12-29 12:15 Daryl Richter <daryl@eddl.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Daryl Richter @ 2005-12-29 12:15 UTC (permalink / raw)
To: pgsql-sql
On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote:
> On Wed, Dec 28, 2005 at 00:52:18 +0700,
> David Garamond <lists@zara.6.isreserved.com> wrote:
>> Is it possible to use only CHECK constraint (and not triggers) to
>> completely enforce ordered value of a column (colx) in a table? By
>> that
>> I mean:
>>
>> 1. Rows must be inserted in the order of colx=1, then colx=2, 3,
>> and so on;
>>
>> 2. When deleting (or updating), "holes" must not be formed, e.g. if
>> there are three rows then row with colx=3 must be the first one
>> deleted,
>> and then colx=2 the second, and so on.
>>
>> I can see #1 being accomplished using a NOT NULL + UNIQUE
>> constraint and
>> a CHECK constraint that calls some PL function where the function
>> does a
>> simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX
>> (colx)+1).
>>
>> But is it possible to do #2 using only constraints?
>
> No. A constraint only applies to one row at a time. If you try to
> work around
> this by calling a function that does queries it isn't guarenteed to
> work.
> And if you are thinking of calling a function that does a query,
> you aren't
> looking at saving time over using triggers.
>
> Also, if you are going to have concurrent updates, you are going to
> need to
> do table locking to make this work.
>
And, finally, you should ask yourself *why* are you doing this, given
that one of the fundamental properties of a table (relation) is that
the rows (tuples) are *unordered.* So much of what makes a
relational db a wonderful thing for storing data depends on this notion.
If you provide an explanation of what you are trying to model,
perhaps we can help you find a better schema design.
[snip]
--
Daryl
(setq email '( daryl at eddl dot us ))
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: constraint and ordered value
@ 2005-12-29 17:48 David Garamond <lists@zara.6.isreserved.com>
parent: Daryl Richter <daryl@eddl.us>
0 siblings, 0 replies; 4+ messages in thread
From: David Garamond @ 2005-12-29 17:48 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Daryl Richter wrote:
>> No. A constraint only applies to one row at a time. If you try to
>> work around
>> this by calling a function that does queries it isn't guarenteed to
>> work.
>> And if you are thinking of calling a function that does a query, you
>> aren't
>> looking at saving time over using triggers.
>>
>> Also, if you are going to have concurrent updates, you are going to
>> need to
>> do table locking to make this work.
>
> And, finally, you should ask yourself *why* are you doing this, given
> that one of the fundamental properties of a table (relation) is that
> the rows (tuples) are *unordered.* So much of what makes a relational
> db a wonderful thing for storing data depends on this notion.
>
> If you provide an explanation of what you are trying to model, perhaps
> we can help you find a better schema design.
Thanks for the insightful answers. Actually I'm just learning about and
trying out CHECK constraints in Postgres (and Firebird) :-)
Regards,
dave
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-29 17:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-27 17:52 constraint and ordered value David Garamond <lists@zara.6.isreserved.com>
2005-12-29 07:16 ` Bruno Wolff III <bruno@wolff.to>
2005-12-29 12:15 ` Daryl Richter <daryl@eddl.us>
2005-12-29 17:48 ` David Garamond <lists@zara.6.isreserved.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