-
Aug 23rd, 2014, 07:36 AM
#1
Thread Starter
Frenzied Member
foreign keys
when creating two tables for relationship, is it really required to setup pk and fk or pk is enough?
-
Aug 23rd, 2014, 07:49 AM
#2
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.
-
Aug 23rd, 2014, 08:00 AM
#3
Thread Starter
Frenzied Member
Re: foreign keys
what if i just setup the pk but did not actually establish the relationship.
-
Aug 23rd, 2014, 08:06 AM
#4
Thread Starter
Frenzied Member
Re: foreign keys
what if the pk is setup but did not establish relationship?
-
Aug 23rd, 2014, 08:22 AM
#5
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.
-
Aug 23rd, 2014, 08:59 AM
#6
Thread Starter
Frenzied Member
Re: foreign keys
as i see it, the use of setting up relationship is enforcing cascading update and delete. do i miss others?
-
Aug 24th, 2014, 02:58 AM
#7
Re: foreign keys
Originally Posted by codesearcher
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...
-
Aug 24th, 2014, 04:10 AM
#8
Re: foreign keys
Originally Posted by codesearcher
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 ?
-
Aug 24th, 2014, 07:30 AM
#9
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
-
Aug 24th, 2014, 08:51 AM
#10
Registered User
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.
-
Aug 24th, 2014, 09:57 AM
#11
Re: foreign keys
Originally Posted by vprog
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
-
Aug 24th, 2014, 12:12 PM
#12
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)
-
Aug 24th, 2014, 12:43 PM
#13
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
-
Aug 24th, 2014, 03:36 PM
#14
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!
-
Aug 24th, 2014, 04:42 PM
#15
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
-
Aug 24th, 2014, 04:49 PM
#16
Re: foreign keys
Originally Posted by szlamany
... 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.
-
Aug 24th, 2014, 05:09 PM
#17
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
-
Aug 24th, 2014, 05:11 PM
#18
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
-
Aug 24th, 2014, 07:52 PM
#19
Thread Starter
Frenzied Member
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.
-
Aug 26th, 2014, 02:53 AM
#20
Re: foreign keys
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
-
Aug 26th, 2014, 05:57 AM
#21
Re: foreign keys
Originally Posted by FunkyDexter
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
-
Aug 26th, 2014, 05:04 PM
#22
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
-
Aug 26th, 2014, 05:25 PM
#23
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.
-
Aug 27th, 2014, 03:39 AM
#24
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
-
Aug 27th, 2014, 03:53 AM
#25
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
-
Aug 27th, 2014, 09:23 AM
#26
Thread Starter
Frenzied Member
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.
-
Aug 27th, 2014, 09:33 AM
#27
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.
-
Aug 28th, 2014, 01:36 AM
#28
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|