Re: [HACKERS] Not In Foreign Key Constraint
2013/9/19 David Johnston pol...@yahoo.com 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. 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
Re: [HACKERS] Not In Foreign Key Constraint
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 problemI've approached it by doingbut 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not In Foreign Key Constraint
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
* *
