On 08/13/2015 05:40 PM, Stephen Feyrer wrote:
On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> wrote:
On 08/13/2015 05:03 PM, Stephen Feyrer wrote:
Hi,
This is probably not an original question merely one which I haven't
been able to find an answer for.
Basically, the question is why is there not an equivalent foreign key
concept to match the primary key we all already know an love?
How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.
Therefore as an example:
{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket
List","user-attribute":"Bucket.List@xxxxxxxxxxx"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@xxxxxxxxxxx"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@xxxxxxxxxxxxxxx"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@xxxxxxxxxxxxx"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@xxxxxxxxxxxxxxx"}}
SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED
This would yield
directory-name directory-name
Bucket List
Supernova
Transactional Relational
Spam
Alternatively linking two user tables - profiles and contacts
profiles
PK-profiles
user-name
real-name
age
gender
region
contacts
PK-contacts
FK-profiles
phone
email
icq
home-page
Getting the user-name and email would look something like:
SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN
When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.
The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.
To me this looks like a good idea.
What happens if you have more then one child table with the same field?
So:
contacts
FK-profiles
....
email
....
vendors
FK-profiles
....
email
....
In that case the result table would look something like:
SELECT email FROM profiles WITH-IMPLICIT-JOIN
'contacts-email','vendors-email'
So what if you want to use a different alias?
What if you only wanted the contacts email and not the vendors?
I see the example below, but now you are changing direction for what I
consider no good reason.
Or to follow a reverse semantic:
SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)
The problem that I see is the current method is self-documenting whereas
implicit joins means you have to 'know' what is implied. This means some
other mechanism to discover what is implied. Seems more complicated then
the present situation.
This would give you
'age','region','email'
Whereas:
For a simple vendors table which might look like:
brand
market
email
rating
SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a
right join)
'brand','region','email'
One point I would like to make clear, is that the foreign key linking
should be a design choice.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
