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?
Many thanks,
Misa