Re: Foreign Keys as first class citizens at design time?

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@example.com"},
>
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supernova@example.com"},
>
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactional@transaction.org"},
>
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmyspam@lovesspam.com"},
>
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relational@transaction.org"}}
>
> 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
    ....


>
>
> --
> Kind regards
>
>
> Stephen Feyrer


--
Adrian Klaver
adrian.klaver@aklaver.com