Re: How to ensure data consistency?
From: Tony Andrews <andrewst_at_onetel.com>
Date: 8 Sep 2004 02:44:09 -0700
Message-ID: <chmk99$4m2_at_odah37.prod.google.com>
ddtl wrote:
> >It would be possible, if Postgres supported the ANSI "CREATE
> >ASSERTION" statement. Presumably it does not?
>
> No, unfortunately postgresql does not support assertions.
>
> If I understand the idea correctly, when such an assertion is in
effect,
> entries into the super-table and sub-table can be made only inside
> a transaction - otherwise, an attempt to insert a row into the main
> table will violate the assertion.
>
> Am I correct?
Date: 8 Sep 2004 02:44:09 -0700
Message-ID: <chmk99$4m2_at_odah37.prod.google.com>
ddtl wrote:
> >It would be possible, if Postgres supported the ANSI "CREATE
> >ASSERTION" statement. Presumably it does not?
>
> No, unfortunately postgresql does not support assertions.
>
> If I understand the idea correctly, when such an assertion is in
effect,
> entries into the super-table and sub-table can be made only inside
> a transaction - otherwise, an attempt to insert a row into the main
> table will violate the assertion.
>
> Am I correct?
Yes, an ASSERTION is a multi-table check constraint something like:
CREATE ASSERTION a
CHECK (NOT EXISTS (SELECT NULL FROM parent
WHERE NOT EXISTS (SELECT NULL FROM child
WHERE child.parent_id =
parent.parent_id))
DEFERRABLE;
(I may not have that quite right: I use Oracle, which doesn't support
ASSERTIONS either :-( )
Received on Wed Sep 08 2004 - 11:44:09 CEST