Results 1 to 28 of 28

Thread: foreign keys

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    foreign keys

    when creating two tables for relationship, is it really required to setup pk and fk or pk is enough?

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: foreign keys

    if you setup the relation in the database, the database engine wil take care of violations i.e. throw an error if you delete the parent while there are still child records.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: foreign keys

    what if i just setup the pk but did not actually establish the relationship.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: foreign keys

    what if the pk is setup but did not establish relationship?

  5. #5
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: foreign keys

    then, from the database perspective, the records do not have a relationship. you can still run your joined selects etc to get the child records so that would not change. its just a little help of the database to ensure data consistency.

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: foreign keys

    as i see it, the use of setting up relationship is enforcing cascading update and delete. do i miss others?

  7. #7
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: foreign keys

    Quote Originally Posted by codesearcher View Post
    as i see it, the use of setting up relationship is enforcing cascading update and delete. do i miss others?
    i dont think you miss anything. but i must admit i do not use relationships set up in databases so my impression might also be incomplete. when i played around with it, it did not reveal such much benefit to me so i did not use it...

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: foreign keys

    Quote Originally Posted by codesearcher View Post
    as i see it, the use of setting up relationship is enforcing cascading update and delete. do i miss others?
    depends on the rdms you are using
    other possiblities could be:
    on delete set null
    on delete set default
    on update set null
    on update set default
    maybe there may be others ?

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    It provides referential integrity. Among the things that includes is: deleting child records when the parent is deleted. Making sure you don't create a child record w/o a parent. Whether you care about the relationship depends on the data and your needs.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Registered User
    Join Date
    Aug 2014
    Posts
    3

    Exclamation Re: foreign keys

    While using two tables, primary key is enough to join two tables. However, in order to increase complexity/ function of the program foreign key can be used.
    i.e if there are duplicate data on primary key, then another foreign key to reduce duplicacy has to be used.
    For Example Table -- empno, empname, deptno
    Table -- empno, deptno, deptname, deptfunction
    Primary key - empno
    if empname is duplicate then foreign key deptno is to be used.

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    Quote Originally Posted by vprog View Post
    While using two tables, primary key is enough to join two tables. However, in order to increase complexity/ function of the program foreign key can be used.
    i.e if there are duplicate data on primary key, then another foreign key to reduce duplicacy has to be used.
    For Example Table -- empno, empname, deptno
    Table -- empno, deptno, deptname, deptfunction
    Primary key - empno
    if empname is duplicate then foreign key deptno is to be used.
    I'm not even going to begin to count the number of things that's wrong with that post other than to point out that foreign keys have nothing to do with duplicate data. Nothing.

    That's not what FKeys are about at all.

    A Foreign Key is a reference to a Primary Key in a different table. Period.

    Let's say I have two tables, Contacts and Addresses. The Contacts table is a list of people:
    FirstName, LastName, Salutation, etc.
    The Address table is a list of their addresses:
    StreetAddress1, StreetAddress2 (they may have a second line in their address), City, State, PostalCode, etc

    So... now how do I relate them to each other? First I give Contacts a PKey:
    ID, FirstName, LastName, Salutation, etc.
    This is also goign to allow me to select, update and delete later (using the ID in a where clause)

    Then I add the FKey to the address table so that it points to the Contact that it belongs to:
    ConstactID, StreetAddress1, StreetAddress2 (they may have a second line in their address), City, State, PostalCode, etc

    I should still add a PKey to the Address table so that I can do select, updates and deletes on specific records:
    ID, ContactID, StreetAddress1, StreetAddress2 (they may have a second line in their address), City, State, PostalCode, etc


    As part of setting up that PKey-FKey relationship, I set it to Cascade Delete, so that when I delete Billy Bob from my Contacts list, it deletes his Address as well.

    This has nothing to do with duplicate data other than generally speaking you cannot have a duplicate PKey value, by default and definition PKeys are unique. Now you can have a Composite PKey... a PKey value that's comprised of TWO (or more) fields, that would allow one or both (or how many fields are part of the key) to be duplicate, but the WHOLE key value should still be unique.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: foreign keys

    A Foreign Key is a reference to a Primary Key in a different table
    usualy that is so, and it is to be advised
    however strictly theoretical it does not have to be a reference to a primary key
    a reference to any candidate key is enough
    (candidate keys are,by definition,unique)

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    True... I thought about introducing Candidate Keys but decided against it to keep it simple. What you use for the relationship - be it a hard or soft one - largely depends on the needs of the system and your data.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: foreign keys

    From a practical standpoint I no longer define FK's - for the most part. I did 15 years ago - not much now...

    The database engine / query optimizer knows what keys are on what tables, so I don't feel the join-syntax will evaluate differently if I define the FK's.

    I personally make sure my UI-logic does not allow activity that is evil - like leaving addresses if you delete a contact record. Actually - most of my apps don't favor deletes. And if I give a customer an option to delete something it's usually the child records.

    But back to the UI - I am doing web apps now - web services/methods - so the database activity is taking place miles away. Stored procedures for CRUD handle the nuances of who gets added / what gets deleted.

    Also - any talk of cascading deletes would have you run out of my shop!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    In our shop, orphaned records will get you run out. Different circumstances, different needs. Our application is built upon a framework that's largely out of (my) control. It's built by the the products division. I just extend it by creating new bells & whistles. So in order by my bell extension to be deleted with the main bell, I have to use the FKey w/ cascading delete. There isn't another mechanism for it. PLus with they type of system there is, there's so many entry and delete points, you never know where it's going to come from.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: foreign keys

    Quote Originally Posted by szlamany View Post
    ... any talk of cascading deletes would have you run out of my shop!
    @tg - when I do/did use FK's it was actually to protect the parent record from deletion in cases where child records are still existing.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    WE do that too. It just all depends on the circumstance. We do have some cases where we can't allow certain records to be deleted. Such as if a financial transaction has been posted to the GL. The FKey from the transaction to the post prevents it from being accidentally deleted (from the back end - the front end disables the delete operation based on the status.)

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    And to just add to that, it just goes to show that it's all dependant on the situation and need. If I ever worked there, I'd probably go into convulsions at no having PK/FK relations... until it clicks and makes sense, and then I'll wonder why I ever bothered with them before.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: foreign keys

    with PK and FK and their relationship AIDS referential integrity and facilitates the ease for cascading updates and deletes but FK and doing relationship may also be not necessary depending on how you design or CODE your application that will ensure referential integrity of both the parent and the child records.

    am I getting it right?
    Last edited by codesearcher; Aug 24th, 2014 at 07:56 PM.

  20. #20
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: foreign keys

    am I getting it right?
    Yes. Basically, orphanned records are a bad thing in a database. You want to make sure that child records can't be created without a parent and parent records can't be deleted while they still have children. You can enforce that in your code by writing explicit checks or you can enforce it in you database by setting up foreign keys. Which you do is up to you and there's no right and wrong.

    Personally I prefer to set up foreign keys in the database. Even if I'm writing code in the application to handle it the foreign keys give me an extra level of protection. On the downside it represents alot of extra work which gives little or no benefit if you're confident that you're application code (and all as yet unwritten application code) is handling everything correctly.

    TG, personally I hate cascading deletes as I'd rather get an error out but it sounds like you've got a decent exception case. Is it dangerous for the main bell to delete your bell, though? Wouldn't you want a process for explicitely deleting your bell?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  21. #21
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: foreign keys

    Quote Originally Posted by FunkyDexter View Post
    TG, personally I hate cascading deletes as I'd rather get an error out but it sounds like you've got a decent exception case. Is it dangerous for the main bell to delete your bell, though? Wouldn't you want a process for explicitely deleting your bell?
    Depends. For Bells? IT's what we want. For Whistles, we might want to throw an error. And there are plenty of cases in our app where that's the case. Like all good things in development, it depends.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  22. #22
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: foreign keys

    letting the database take care off the referencial integrity
    or letting the code take care of it
    does not have to be an or/or choice
    it can be and/and
    and, as carefull as a programmer may be to make sure the integrity is not violated
    can you, as a programmer ever be sure, every program made by any other programmer
    to acces the same database, will be as carefull as your program

  23. #23
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: foreign keys

    There is a cost to having FK's that should be taken into account - it's worth mentioning.

    Let's assume, for sake of argument, that an INSERT into a table "costs" a single I/O. And along that same argument - having an INDEX on that same TABLE will cost another one I/O unit - now you cost two.

    That's why we don't make every field an index - 10 indexes would take 11 times longer to add a new record.

    A foreign key behaves in a similar fashion - it needs to check that "other table" - do an I/O operation - to make sure that referential integrity is adhered to.

    I develop enterprise level applications for large operations. Even in an entire town hall - entering Purchase Orders, for instance - only happens in a single place in my application. The LEDGER_T table needs a record in the PO_T for these types of entries. Do I need a FK to make sure the UPDATE STORED PROCEDURE (the "U" in CRUD) adheres to this rule? I don't.

    Take Journal Entries, for example. Still only a single place where they are entered by the user - maybe two actually. And also dozens of "routines" that mass load JE records. These ones want speed - and they are more controlled (as probably in a single SPROC anyway). They also don't need FK's - in reality they are so "fixed" in nature that they aren't going to cause problems.

    Then you take the reality of managing large operations for long periods of time (I've got customers that date back to 1988 still with me today - with several tables of multi-million row counts).

    Let's say a complex change comes down from management. Sure having FK's would help us not "breach protocol" as we beat up the existing code with the enhancements. In the long run you have to visit every offending place and add new logic anyway.

    From my own perspective I do not like to use TRIGGERS - too much business logic buried in a kind of too-deep of a place.

    I believe it's that "point of view" that has led me to use less and less FK's in my new database designs.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #24
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: foreign keys

    there is a cost to having FK's that should be taken into account
    Very true and easy to overlok. We tend to think about database performance in terms of reads but that's not all that's going on. If you have a very hign throughput of writes then foreign keys could start to have a significant impact.

    I do not like to use TRIGGERS
    I'm with you on that one. Triggers are the work of the divil.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  25. #25
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: foreign keys

    Very true
    the cost of having fk should be considered
    just as the possible cost of an inconsistend db should be considered
    Triggers are the work of the divil
    Very true, esspecially the ones you trigger with your index finger

  26. #26

    Thread Starter
    Frenzied Member
    Join Date
    May 2013
    Posts
    1,126

    Re: foreign keys

    wow, i though i am only one who is thinking of not using triggers and fk cost. its really great to be part of a community to learn with others experiences and point of views.

  27. #27
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: foreign keys

    Look - the database is your tool - kind of like a car.

    Lots of features. You can click the directional lever to indicate a left turn - and click it off when you are done. Or let it click off automatically. Or don't fully depress it - just hold it a bit down and it flashes left. It's just a directional. You are in control of getting where you want.

    I put all my logic - all my business logic - in STORED PROCEDURES. I save these as SCRIPT files - .SQL text files. I've created SEARCH tools so I can work this pile of business logic and keep it in line.

    Searching for INSERT INTO SOMETABLE when making code changes - to see if I am breaking my database "rules of integrity" - is no great effort. Digging into the OBJECT BROWSER WINDOW to see if a FK is defined? I'm not sure how to even do that easily...

    It's not about just the creation of an application - it's a life cycle of enhancements. How do you want to manage that? Conventions - all that stuff - make a whole more of an impact on the successful life cycle of some application (what's it going to be anyway - 10 years? Maybe 15 if you are really lucky.)

    That's how I drive my car.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  28. #28
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: foreign keys

    Another thing worth to consider is that relationrules set up in the database as well as constraints will enforce data integrity for the entire lifetime of your application and even beyond that. if its a commercial business application then you need to face it: at one point in time there will be someone (might even be yourself) executing direct sql in the database bypassing your application logic. reasons for this can be various. if you have set up relations/constraints the database will ensure that potential integrity damage is minimized.

    triggers certainly need to be used with care because of the already mentioned reasons. when doing mass data loading, triggers will need be be deactivated most of the time to avoid slowdown.

    szlamany, instead of searching in your .sql files you could also let the database do that:
    Code:
    SELECT *
    FROM sys.sql_modules
    WHERE definition like '%INSERT INTO SOMETABLE%'
    basically something similar should be possible for searching for fk definitions and constraints but i am sure you know that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width