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(at)Yahoo(dot)com #