Re: FK v.s unique indexes

W dniu 04.07.2018 o 00:55, David G. Johnston pisze:
> On Tuesday, July 3, 2018, Rafal Pietrak <rafal@ztk-rp.eu
> <mailto:rafal@ztk-rp.eu>> wrote:
> 
> 
>     ERROR:  there is no unique constraint matching given keys for referenced
>     table "test2"
>     ----------------------------
> 
>     I cannot see any reasons why this functionality is blocked.
> 
>     In particular, contrary to what the ERROR says, the target table *does
>     have* a "unique constraint matching given keys", admittedly only
>     partial.
> 
> 
> You are making the common error of confusing the distinct concepts of
> constraints and indexs.  Table constraints cannot be partial by
> definition, and are a logical concept constraining the data model. 

Hmmm..

This does not match "my reality". Naturally I may be wrong, but the
example I've posted reflects my actual data I'm putting into the RDBMS.
That is:
1. the data has unique constraint on (load,a,b,c)
2. and the data have additional unique constraints on (load,a), provided
c is true, and (load,b) whenever c is false.

Pls consider in real life: load (a person), can have either a (a kind of
brest cancer); or b (a kind of prostrate) - this is only a cooked
example attemping to illustrate, that one may need to put additional
constraints on the entire dataset.

I'm creating partial indexes *meaning* enforcing constraints to:
1. avoid accumulation of dataset errors due to illegal data entry, and...
2. to allow for other tables to reference only parts of the main table.
Those other tables contain data relevant only to the parts of main table
they are referring. As of now, to have it FK to main table, I have to
unnecessarily keep in those other tables constant data, which is
"obvious" to them (like b & c values, which are irrelevant for unique
<load,a> cases).

But. IMHO, my usage of indexes in this case is actually setting up a
partial constraint. As I cannot use them for FK, there is no real use
for them as query execution support. And if I drop them, I'm actually
allowing data in my table, which is inconsistent with reality .... so
they actually do play a role of constraints.

So I do have data which require partial constraints. I could have make
them as sort of "checks". But it is much easier for me to just have a
partial unique index. And it's very naturally meaningful to anybody
"reading the schema" in the future. I don't think there is any harm in it.


> Indexes are physical objects that only aid in the execution of queries. 
> The only crossover is that the implementation of a unique table
> constraint uses a full unique index as an implementation detail.
> 
> The error says 'constraint' and indeed you have nit defined a relevant
> constraint in your schema, just indexes.

Yes, but my goal isn't just enforcing constraints. My goal is to make
other tables refer to the main datatable with as short FK as possible.
To make those references (in postgresql) I have to have a unique index.
Still, I cannot do that on partials..... My question is: Why? Why
postgresql have introduced such restriction. It serves no purpose.
Without it, everything would just work (I think :).


-R