Re: pg_dumpall and check constraints

Philip Warner wrote:
> At 17:56 30/06/00 +0200, Jan Wieck wrote:
> >
> >    For  gods  sake  they  don't have.  And I'm uncertain that it
> >    should ever work.
>
> Sorry...I'm the one to blame for the suggestion. My only defense is it was
> late, and I was misled by the parser...never the less...

    Philip,  I'm far from blaming anyone for an idea or question.

> >    How should a RESTRICT  or  ON
> >    DELETE CASCADE work in that scenario?
>
> Perhaps as Check constraints on all tables in the view...for the most part
> I would not expect complex views to be used in this way, but since this is
> what the user would have to do anyway, why not do it for them?

    Was late for me too, and maybe the answer was  too  lazy.  So
    let me give you an example of what I meant:

        CREATE TABLE t1 (
            a     integer,
            b     integer
        );

        CREATE TABLE t2 (
            a     integer,
            c     integer
        );

        CREATE VIEW v1 AS SELECT t1.a, t1.b + t2.c AS d
            FROM t1, t2 WHERE t1.a = t2.a;

    Not that complex so far. Now we create a reference as

        CREATE TABLE t3 (
            x    integer,
            y    integer,
            z    integer,
            FOREIGN KEY (y,z) REFERENCES v1 (a,d)
        );

    The  first  problem  arising from it is that we are unable to
    create  a  UNIQUE  constraint  for  v1(a,d),   which   is   a
    requirement of referential integrity as of the SQL specs. The
    system doesn't check if a UNIQUE constraint exists up to now,
    even for real tables, but that's another story.

    The second problem is how should the system check on a

        DELETE FROM t2 WHERE c < 10;

    if  this would cause any referenced key to disappear from v1?
    Can it do anything else than a sequential scan on t3 and then
    evaluating v1 for each row found?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #