Thread: Foreign keys/unique values and views
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? 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! - Einar Karttunen
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
On Thu, 22 Mar 2001, Richard Huxton wrote: > > 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. if one can reference a table with a foreign key it makes possible to reference inheritance hierarchies. If I create a view from the parent with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain the entries from the children as well. Now if I could reference this view I could simulate referencing parent and child tables easily. What I have in mind is a hierarchy of persons all inheriting from a table called person. Different kinds of persons have different attributes but all have an id and name. What I want is that other tables could reference these persons easily. - Einar Karttunen
On Thu, 22 Mar 2001, Einar Karttunen wrote: > On Thu, 22 Mar 2001, Richard Huxton wrote: > > > > 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. > if one can reference a table with a foreign key it makes possible to > reference inheritance hierarchies. If I create a view from the parent > with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain > the entries from the children as well. Now if I could reference this view > I could simulate referencing parent and child tables easily. > What I have in mind is a hierarchy of persons all inheriting from a table > called person. Different kinds of persons have different attributes but > all have an id and name. What I want is that other tables could reference > these persons easily. Currently the reltype of the referenced thing has to be a table, plus I don't think you can currently put a unique constraint on views either which would be a prerequisite. The reason for this is precisely the kind of thing you're trying to do. The system doesn't currently know enough to be able to do this. With the triggers, we'd have to add triggers to the parent and children table for update and delete, but we'd have to know what they were, and if you inherit a new table, that one would need it to. Plus, what if the view has where clauses, etc, you have to work out when you would need to limit things, etc. If we ever do enough stuff to allow generic updatable views (beyond really simple ones), we'd probably be able to do this, but until then...
From: "Einar Karttunen" <ekarttun@cs.Helsinki.FI> > On Thu, 22 Mar 2001, Richard Huxton wrote: > > > > 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. > if one can reference a table with a foreign key it makes possible to > reference inheritance hierarchies. If I create a view from the parent > with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain > the entries from the children as well. Now if I could reference this view > I could simulate referencing parent and child tables easily. > What I have in mind is a hierarchy of persons all inheriting from a table > called person. Different kinds of persons have different attributes but > all have an id and name. What I want is that other tables could reference > these persons easily. Ah - makes sense. I must admit I don't use inheritence/arrays since I never know what platform I'll end up running on, so I like to keep things to standard SQL where possible. Trying the obvious (reference an id field in the parent) fails on execution: referential integrity violation - key referenced from bar not found in foo_parent You also can't reference the id field in the child table. There's a question on this on the pgsql-sql list (from Johannes Groden - dated today) but I don't see any replies yet. This sounds like something Tom/Bruce will need to get involved in. - Richard Huxton
Einar Karttunen wrote:
> On Thu, 22 Mar 2001, Richard Huxton wrote:
> >
> > 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.
> if one can reference a table with a foreign key it makes possible to
> reference inheritance hierarchies. If I create a view from the parent
> with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain
> the entries from the children as well. Now if I could reference this view
> I could simulate referencing parent and child tables easily.
> What I have in mind is a hierarchy of persons all inheriting from a table
> called person. Different kinds of persons have different attributes but
> all have an id and name. What I want is that other tables could reference
> these persons easily.
Inheritance isn't supported in FOREIGN KEY, and cannot.
Remember that it's not only the check if the referenced key
exists on FK insert and update. FOREIGN KEY ensures that you
cannot remove those PKs or has automated actions defined for
that case. We don't have trigger inheritance. And since one
of the requirements of FOREIGN KEY is that you have a UNIQUE
constraint on your PK, this cannot be done with FOREIGN KEY.
How do you put a UNIQUE constraint on the entire inheritance
hierarchie?
You can create custom triggers that check for whatever you
want. But FOREIGN KEY has to follow the SQL specs. All
things fit together, if one doesn't fit, don't force it - use
a bigger hammer. We have sledge hammers available in all
sizes :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Thu, Mar 22, 2001 at 06:33:16PM -0500, Jan Wieck wrote: > Einar Karttunen wrote: > How do you put a UNIQUE constraint on the entire inheritance > hierarchie? Easy. You make a unique index that covers an entire inheritance hierarchy. If lots of table inherit a field "id" from a single table the you can put a unique index on that field. All you need then is an index whose keys reference not tuple IDs but (table ID, tuple ID) pairs. Wow. I'm just thinking of how vacuum would deal with this. Probably not very well. But it would give inheritance hierarchies very efficient lookups rather than the implicit unioning done currently. Martijn