Foreign keys - one to one, one to many relationship

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Foreign keys - one to one, one to many relationship

csanyipal
Hi,

on Gentoo linux I have installed sqlite3 version 3.19.3.

With which constraints can one create a foreign key to
first: get one to one relationship, and
second: one to many relationship?

--
Best, Pali
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Simon Slavin-3


On 14 Oct 2017, at 10:30am, Csányi Pál <[hidden email]> wrote:

> With which constraints can one create a foreign key to
> first: get one to one relationship, and
> second: one to many relationship?

Foreign keys always implement a one-to-many relationship.

You could enforce a one-to-one relationship by using one foreign key in each direction.  But there could be problems when getting the data in in the first place — which one do you put in first ?

<https://sqlite.org/foreignkeys.html>

If you have problems with the above page, please post again.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Richard Damon
On 10/14/17 5:35 AM, Simon Slavin wrote:

>
> On 14 Oct 2017, at 10:30am, Csányi Pál <[hidden email]> wrote:
>
>> With which constraints can one create a foreign key to
>> first: get one to one relationship, and
>> second: one to many relationship?
> Foreign keys always implement a one-to-many relationship.
>
> You could enforce a one-to-one relationship by using one foreign key in each direction.  But there could be problems when getting the data in in the first place — which one do you put in first ?
>
> <https://sqlite.org/foreignkeys.html>
>
> If you have problems with the above page, please post again.
>
> Simon.
One way to make them build one to at-most-one relationships is too add a
unique index on the foreign key, then it can only occur once.

A True one-one relationship by the normailization rules says that they
should all be in the same record.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Stephen Chrzanowski
In this case, rules can be made to be broken.  In the case of larger blobs,
I push those out to a different table, and enforce (Through my software,
not a unique constraint as mentioned) the one-to-one relationship.  There
is no reason to keep the blob on a record that is queried constantly.

On Sat, Oct 14, 2017 at 6:37 AM, Richard Damon <[hidden email]>
wrote:

One way to make them build one to at-most-one relationships is too add a

> unique index on the foreign key, then it can only occur once.
>
> A True one-one relationship by the normailization rules says that they
> should all be in the same record.
>
> --
> Richard Damon
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Richard Damon
Yes, sometime for performance reasons you need to break the rules of
normalization.

I did think that if the blob was at the end of the record, sqlite
wouldn't read that part of the record unless asked to (so you aren't
gaining that much with the separate table).

If the second table is just the foreign key and the blog, I would think
that my idea of making the foreign key a unique index (maybe even the
primary key for that table), would give you most of what you want. Yes,
you could create a record in the other table without a corresponding
record in the blob table, but that is sort of needed to avoid chicken
and egg issues with creating new records (not sure when foreign key
requirements are tested), and sort of represents that the blob is
empty/not written yet.

On 10/14/17 12:35 PM, Stephen Chrzanowski wrote:

> In this case, rules can be made to be broken.  In the case of larger blobs,
> I push those out to a different table, and enforce (Through my software,
> not a unique constraint as mentioned) the one-to-one relationship.  There
> is no reason to keep the blob on a record that is queried constantly.
>
> On Sat, Oct 14, 2017 at 6:37 AM, Richard Damon <[hidden email]>
> wrote:
>
> One way to make them build one to at-most-one relationships is too add a
>> unique index on the foreign key, then it can only occur once.
>>
>> A True one-one relationship by the normailization rules says that they
>> should all be in the same record.
>>
>> --
>> Richard Damon
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Keith Medcalf
In reply to this post by Richard Damon
On Saturday, 14 October, 2017 04:37, Richard Damon <[hidden email]>, wrote:

>One way to make them build one to at-most-one relationships is too
>add a unique index on the foreign key, then it can only occur once.

>A True one-one relationship by the normailization rules says that
>they should all be in the same record.

Only for lower levels of normalization.  At the higher normalization levels optional 1:1 related data, particularly any which contains meaningful relations between the candidates and data of that relation of its own, goes into its own relation joined to the parent through a 1:1 relationship.

It can also be viewed as a physical layer implementation optimization where no such requirement exists from normalization to move the optional relation into its own 1:1 relation.

Of course, if you normalize all the way up to where you end up with a "virtual master" containing only relationships to the actual data containing relations, then this will always be in a 1:1 relation with the master.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign keys - one to one, one to many relationship

Wolfgang Enzinger
In reply to this post by Richard Damon
Am Sat, 14 Oct 2017 13:01:59 -0400 schrieb Richard Damon:

> you could create a record in the other table without a corresponding
> record in the blob table, but that is sort of needed to avoid chicken
> and egg issues with creating new records (not sure when foreign key
> requirements are tested), [...]

Depends on how the foreign key was defined; in order to avoid chicken and
egg issues, a Deferred Foreign Key Constraint is recommended:

http://www.sqlite.org/foreignkeys.html#fk_deferred

AFAIK, at least. ;-)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users