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.
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.
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.
--jkl
