Thread: Not In Foreign Key Constraint

Not In Foreign Key Constraint

From
Misa Simic
Date:
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








 

Re: Not In Foreign Key Constraint

From
Jim Nasby
Date:
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



Re: Not In Foreign Key Constraint

From
David Johnston
Date:
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.



Re: Not In Foreign Key Constraint

From
Misa Simic
Date:

2013/9/19 David Johnston <polobo@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: Not In Foreign Key Constraint

From
David Johnston
Date:
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.