Thread: FK pointing to a VIEW

Do I understad correctly that i cannot point a Foreign Key to a view? Which
is the rationale of this?

TIA
sandro
*:-)



test=# alter table mail_inviate
test-#    add constraint mail_inviate_fk
test-#    FOREIGN KEY (mittente) REFERENCES mail_view(mail_address)
test-# ;
ERROR:  referenced relation "mail_view" is not a table

--
Sandro Dentella  *:-)
http://www.tksql.org                    TkSQL Home page - My GPL work

On 11/10/06, Sandro Dentella <sandro@e-den.it> wrote:
> Do I understad correctly that i cannot point a Foreign Key to a view? Which
> is the rationale of this?

Blame the sql standard.  Foreign keys are required to reference a
table with a unique constraint, and you can't add a unique constraint
to a view.  While I agree in principle that such a thing should be
able to be done, it simply isn't possible.  (in PostgreSQL, you can't
even add an index to a view, which a unique constraint would depend
on).

merlin

Sandro Dentella <sandro@e-den.it> schrieb:

> Do I understad correctly that i cannot point a Foreign Key to a view? Which
> is the rationale of this?

A VIEW is simply a regular SELECT ..., a string that contains a SELECT.

Question: How can you add a FK to a string? You can add a FK to a table,
no problem, but to a simple string?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> While I agree in principle that such a thing should be
> able to be done, it simply isn't possible. (in PostgreSQL, you can't
> even add an index to a view, which a unique constraint would depend
> on).

Agreed on that.

But such an extension would require a view to be more than just SELECT.

------------------------------
Olexandr Melnyk,
http://omelnyk.net/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On 10 Nov 2006, at 20:47, Olexandr Melnyk wrote:

> > While I agree in principle that such a thing should be
> > able to be done, it simply isn't possible. (in PostgreSQL, you can't
> > even add an index to a view, which a unique constraint would depend
> > on).
>
> Agreed on that.
>
> But such an extension would require a view to be more than just
> SELECT.
>
> ------------------------------
> Olexandr Melnyk,
> http://omelnyk.net/
>

This would mean something like an index spreading over more then one
table in the end, or did I miss something ?

- --

Viele Grüße,
Lars Heidieker

lars@heidieker.de
http://paradoxon.info

- ------------------------------------

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
      -- Friedrich Nietzsche



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFVRKiDAkIK9aNPuIRAgh9AJ97OrIyiaSCAn5lwxLtPFG6B7CtdQCfYyRf
Aypj7GBygMNAxVEmYonkL3o=
=hg/j
-----END PGP SIGNATURE-----

Looks like I've missed your mail, so a late reply.

2006/11/11, Lars Heidieker <lars@merlin.de>:

> > > While I agree in principle that such a thing should be
> > > able to be done, it simply isn't possible. (in PostgreSQL, you can't
> > > even add an index to a view, which a unique constraint would depend
> > > on).
> >
> > Agreed on that.
> >
> > But such an extension would require a view to be more than just
> > SELECT.
>
> This would mean something like an index spreading over more then one
> table in the end, or did I miss something ?

Yes. But that is hardly implementable.

------------------------------
Olexandr Melnyk,
http://omelnyk.net/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On 28 Nov 2006, at 13:33, Olexandr Melnyk wrote:

>
> Looks like I've missed your mail, so a late reply.
>
> 2006/11/11, Lars Heidieker <lars@merlin.de>:
>
> > > > While I agree in principle that such a thing should be
> > > > able to be done, it simply isn't possible. (in PostgreSQL,
> you can't
> > > > even add an index to a view, which a unique constraint would
> depend
> > > > on).
> > >
> > > Agreed on that.
> > >
> > > But such an extension would require a view to be more than just
> > > SELECT.
> >
> > This would mean something like an index spreading over more then one
> > table in the end, or did I miss something ?
>
> Yes. But that is hardly implementable.
>

I think so too, propagating the changes in one of the views
underlying tables will be really hard,
as than the index of the view must be maintained as well as the
change to the view might cause
cascading...
While otherwise a view is simply a view, I don't know in how far this
can be done by something like
a materialized view (I think Oracle and DB2 etc have those)

- --

Viele Grüße,
Lars Heidieker

lars@heidieker.de
http://paradoxon.info

- ------------------------------------

Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
      -- Friedrich Nietzsche



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)

iD8DBQFFbFcIDAkIK9aNPuIRAoS/AJ9rvEwzTJrMkGAJ0PWUFFo/ftBCEACcCENd
nG0yYwita4L3nr4Tg0IJ7oU=
=kMo/
-----END PGP SIGNATURE-----

On Tue, Nov 28, 2006 at 03:33:54PM +0200, Olexandr Melnyk wrote:
> >This would mean something like an index spreading over more then one
> >table in the end, or did I miss something ?
>
> Yes. But that is hardly implementable.

Actually, an index over multiple tables is not really the hard part.
It's setting it up so you don't cause deadlocks that's tricky. And what
people really want is *unique* indexes over multiple tables, but there
the locking considerations are even worse.

My gut feeling is that it actually won't be that bad once someone hits
on the right idea and codes it up, but I've been known to be wrong
before.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.