Re: Not In Foreign Key Constraint

On 9/16/13 6:16 AM, Misa Simic wrote:
> Hi hackers,
>
> I just wonder how hard would be to implement something like "Not In FK Constraint" or opposite to FK...
>
> i.e:
>
> FK ensures that value of FK column of inserted row exists in refferenced Table
>
> NotInFK should ensure that value of NotInFK column of inserted row does not Exist in referenced Table...
>
>
> The only difference/problem I see is that adding that constraint on an Table - Forces the same Constraint on another table (but in opposite direction)
>
>
> i.e.
>
> TableA(tableA_pk, other_columns)
> TableB(tableb_fk_tableA_pk, other_columns)
> TableC(tablec_notInfk_tableA_pk, other_column)
>
>
> each _pk column is Primary Key of its Table
> TableB has on PK FK to TableA on the same time...
>
> INSERT INTO TableA VALUES ('tableAPK1', 'somedata')
>
> INSERT INTO TableB VALUES ('tableAPK1'. 'somedata')
>
> everything ok,
>
>
> now, we would like to Add NotInFK on TableC To TableA
>
> INSERT INTO TableC VALUES ('tableAPK1'. 'somedata')
>
> Should Fail - because of 'tableAPK1' exists in TableA
>
> INSERT INTO TableC VALUES ('tableAPK2'. 'somedata')
>
> Should pass - because of 'tableAPK2' does not exist in TableA...
>
> How ever, now
>
> INSERT INTO TableA VALUES ('tableAPK2'. 'somedata')
>
> should fail as well - because of that value exists in TableC
>
>
> I guess that rule can be achieved with triigers on TableA and TableC - but the same is true for FK (and FK constraint is more effective then trigger - that is why I wonder would it be useful/achievable to create that kind of constraint)
>
> Thoughts, ideas?

You're unlikely to find much support for this without use cases.

Why would you want an "Anti-FK"?
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net