Re: [PATCH] Support for foreign keys with arrays

Hi Kevin,

Well, from my point of view "many-to-one" or "one-to-many" is more related from which point we are looking to the thing... But let me better explain what I thought... 

Example 1)

If we have one table with master data TableA(ID, other properties...) and TableB(tableAIDFK, propA, propB, propC) -PK of TableB is irrelavant in this point... and let say a lot of TableA tuples could have the same TableB properties... So we can have how many common TableA tuples, that many tuples in TableB with the same values in PropA, PropB, and PropC with FK the same type as in Table A, or to have 1 tuple in TableB with Array type as FK field.... So it (1 tuple in TableB) can point many tuples in TableC... And in the same time simple element can exist in TableA, but could or doesn't have to exist in TableB...

What test would show is there any gain in this approach - I don't know... but think it should - especially if propA,PropB, and C should be updated for all of them...

Example 2)

From other side, what Jeff propose, and what is also usefull, but different thing is... to have the main data in TableA, but key field is an range datatype... what later each element what belong to the range, could have related tuple in TableB (Also, as the same range datatype - but smaller... contained by Master one... or simple datatype subtype of the range) - which is other way around... Opposite from exmaple 1 - but differnet from functional point of view... Depending what is the Master... Also, for example 1 - data in FK do not need to be in range.. so basicaly ID [1, 2 ,4 ,7] could have 1 tuple with its properties, and [3,5,6] in second tuple with different properties...

I am not sure Example 2) Jeff called "Inclusion Constraint" - Jeff can explain it better :)

Based on

I have understood it as:

TableA(ID, properties...)

TableB(ID, properties...)

Now if we define FK on TableB to TableA... It means that row inserted in TableB, must have already row with the same ID value in TableA...

But what would be usefull, to define Exclude FK to table A,  to we prevent insert new row in Table B with ID value what already exist in TableA...

btw, if anyone is happy to point me in right direction, and there is common feeling it is usefull feature, I am happy to code it... Actually that is something what I will code anyway for "in-house" solution - but would be good to do it under Postgres standards...

Kind Regards,

Misa