On Wed, 18 Aug 2004, Josh Berkus wrote: > > In the case that a table constraint is a referential constraint, > > the table is referred to as the referencing table. The referenced > > columns of a referential constraint shall be the unique columns of > > some unique constraint of the referenced table. > > Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is > NOT a requirement of Relaitonal Algebra. So why does SQL require it? > > Maybe I'll ask Joe Celko after he finishes moving to Austin. > > I have my own issue that forced me to use triggers. Given: > > table users ( > name > login PK > status > etc. ) > > table status ( > status > relation > label > definition > PK status, relation ) > > the relationship is: > users.status = status.status AND status.relation = 'users'; > > This is a mathematically definable constraint, but there is no way in standard > SQL to create an FK for it. This is one of the places I point to whenever > we have the "SQL is imperfectly relational" discussion.
Well, I think SQL does give a way of specifying that constraint through assertions and check constraints with subselects. We just don't support either of those constructs. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
