Thread: Not In Foreign Key Constraint
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
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
ismore 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@nasby.net
512.569.9461 (cell) http://jim.nasby.net
Misa Simic wrote > 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 create a common "keys in use" table and only insert a record into the main tables if you can successfully add the desired key to the shared keys table ( as a unique value ). Setup a normal FK to that table to help enforce that valid records must exist on the keys table. Not fool-proof but you only need to worry about insertions - delete from the pk table to remove the record from the main table and free up the key. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Not-In-Foreign-Key-Constraint-tp5771056p5771546.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
2013/9/19 David Johnston <polobo@yahoo.com>
Misa Simic wrote> I guess that rule can be achieved with triigers on TableA and TableC - butYou create a common "keys in use" table and only insert a record into the
> 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?
main tables if you can successfully add the desired key to the shared keys
table ( as a unique value ). Setup a normal FK to that table to help
enforce that valid records must exist on the keys table. Not fool-proof but
you only need to worry about insertions - delete from the pk table to remove
the record from the main table and free up the key.
David J.
Thanks David,
Yes, that is one of ways that goal can be achieved via triggers (or to let someone else worry about that Key is inserted/updated/deleted in Master Table first...)
Constraint - should be more effective way... (It shouldnt be mixed with FK constraint - even it is opposite on some kind... - it was just simplest way to describe the feature)
And it should ensure that every row in table is valid from moment it is created (what trigger can't ensure - constraint does it - or constraint cant be created etc)
Thanks,
Misa
Misa Simic wrote > Hi hackers, > > I just wonder how hard would be to implement something like "Not In FK > Constraint" or opposite to FK... A more useful couple next sentences would be along the lines of: I have this problem....I've approached it by doing....but it seems that an actual database enforced constraint would be a better solution. Is that something that has been considered? Are their other ways of attacking this problem I have not considered? You took quite a bit of time to try and start a discussion, and I get that you don't necessarily know where it is going to lead, but "Not In FK constraint", with a descriptive sentence of two, likely would have been enough to get the ball rolling. Instead you devoted more space to technical clarification that would have been better served by espousing on "what problem" and how current approaches to dealing with said problem are limited. A more specific end-question would also help solicit better responses. I say all this because 3 days later nothing more substantial than "why is this feature necessary" has been put forth. The general idea likely has some merit but you've not provided anything for people to hook their teeth into. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Not-In-Foreign-Key-Constraint-tp5771056p5771651.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.