On 2016/05/19 12:51 AM, James K. Lowden wrote:
> On Wed, 18 May 2016 19:06:30 +0200
> R Smith <rsmith at rsweb.co.za> wrote:
>
>>> I'm not convinced the requirement that the referenced columns be
>>> unique is justified
>> How do you see a parent-child relationship possible where the parent
>> is not Unique?
> I think I can convince you that uniqueness is a good rule of thumb, but
> that enforcing it ahead of RI is undesirable. But the price of making
> me think about it is reading a long answer.
Serves me right :)
Thank you for elaborating.
> The referenced table represents the domain of the foreign key
> relationship. When we say
>
> foreign key R(A) references S(B)
>
> we're saying every value in A appears in B. We're not saying anything
> about B: not its type, not its cardinality. The statement describes A
> only.
>
> We're also saying something logical, not enforced by the DBMS: that R
> is a part of S. S can exist without R, but R without S is
> meaningless. But that's a modelling question, and I can't think of
> another SQLite feature that enforces any aspect of database design.
> Why start here of all places? Was that even the intention?
>
> So lets's look at my table T and its FK
>
> , foreign key (SegName, DbdName) references
> Segm(Name, DbdName)
>
> as it happens, Segm was defined with
>
> , Name text not NULL primary key
> , DbdName text not NULL
>
> One day, Segm may have a 2-column PK: primary key (Name, DbdName).
> But today, Name uniquely identifies it. (Note that therefore {Name,
> DbdName} also uniquely identifies it!) T extends Segm, and requires
> that its {SegName, DbdName} pair appear in Segm.
Ok, I'm convinced, but for the assumption that you've suggested
non-uniqueness before, which is of course not the case.
I suppose it boils down to suggesting that a DB accepts in a Foreign Key
relation, as a parent, any combination of references which is unique by
virtue of combined uniqueness, or if any one (or more) of the
constituent references in itself is unique. (Since if A is unique, it
follows that A|B|C|... is unique for any and all possible values of B,C,...)
I think SQLite has an additional difficulty in that it needs to have a
KEY Index to implement the mechanism of cascading changes, or at least,
testing whether the changes require cascading to children. This (if it
even is accurate) is a peculiarity or implementation detail though.
>
> Let's assert that's *correct*, even though Segm.Name is unique today.
> What is *wrong* with saying the FK relationship refers to more columns
> than are in the domain table's PK? After all, the above assertions are
> still true:
>
> 1. T{SegName, DbdName} must be in Segm{Name, DbdName}
> 2. T extends Segm
>
> Even more -- though not required IMO -- Segm{Name, DbdName}is unique
> (because Segm{Name} is unique).
>
> You could probably get me to agree that the relationship is anomalous.
> I suppose if Segm.Name is unique, the FK should refer only to it.
>
> In general, though, not every domain is manifested in a table. One
> might have these PKs:
>
> S {A, B}
> R {B}
>
> Now let me assert that R extends S: that is, for any S there could be
> an R. The rule: If an R exists for S, there is only one,
> regardless of A.
>
> If that's logically incoherent, I don't understand why.
>
> Remember, there could be a missing domain table, say, T {B}, and the
> real rule would be that for some T there must be an R. But T is
> missing because it has no non-key attributes, and S serves in its
> stead.
>
> That's where "not sure justified" comes from. foreign_key_check
> nudges the user in the right direction most of the time, and as a
> linter I have no argument with it. However, as implemented, "foreign
> key mismatch" prevents reporting of a genune error, namely "FOREIGN KEY
> constraint failed". By my lights that's putting a design
> recommendation before a data error, definitely cart before horse.
>
> I hope that one day FK enforcement becomes a property of the database,
> not of the connection. If that comes to pass, this issue needs careful
> consideration. As things stand, I think it might be better if "foreign
> key mismatch" were demoted to a warning.
Agreed.
Cheers,
Ryan
