Re: Foreign keys/unique values and views

From: "Einar Karttunen" <ekarttun@cs.Helsinki.FI>

> Hi
>
> I was wondering whether there is anyway to use unique values in views.
> If I create a view it drops all unique definitions. Is it possible
> to reference a view with a foreign key?

With a read-only view the data will reflect the underlying tables, so no
problem there. If you make a view updatable with your own triggers then what
happens on an update/insert is up to you. Regardless, the constraints on
underlying tables should be respected anyway.

I have to admit I've never tried referencing a view with a foreign key. I
don't know if it's possible and I have to admit the idea makes me
uncomfortable. Can't give a good reason why, but I'd apply constraints at
the table level.

> for example:
> >test=# CREATE TABLE ex1 (c1 int PRIMARY KEY);
> >NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'ex1_pkey'
> >for table 'ex1'
> >CREATE
> >test=# CREATE TABLE ex2 (c2 int,col char[4]);
> >CREATE
> >test=# CREATE VIEW v AS SELECT * FROM ex1, ex2 WHERE c1=c2;
> >CREATE
> >test=# \d v
> >               View "v"
> > Attribute |      Type      | Modifier
> >-----------+----------------+----------
> > c1        | integer        |
> > c2        | integer        |
> > col       | character(1)[] |
> >View definition: SELECT ex1.c1, ex2.c2, ex2.col FROM ex1, ex2 WHERE
> >(ex1.c1 = ex2.c2);
> c1 is here not unique!

No - because you've made a join between ex1 and ex2. If ex2 contains
duplicate values of c2 then you'll get multiple lines in the view.

A view is nothing more than a "pre-rolled" query. If you only want one line
for every value of c1 you need to design your query accordingly:

CREATE VIEW v AS SELECT c1,min(c2),min(col) FROM ex1,ex2 WHERE c1=c2 GROUP
BY c1;

Or similar, depending on what you are after.

- Richard Huxton