Tables with Foreign key reference
-
Hi guys
I'm having a headache with deleting a table with foreign key reference. How can I delete from the table because I know truncating is not possible, i've tried no check but still no luck.
M
-
Delete the data in the child table that references the data you want to delete from the parent table. Then you will be able to delete the parent data. Alternatively, you can recreate the constraint so that it cascades deletes. Then any data you delete from the parent data will automatically cause the child data to be deleted as well.
John
-
unfortunately there's more than one reference table, so deleting from the child tables will take a long time.
-
That's what you have to do unless you remove the FK reference.
The references usually exist so that you don't get child data that's orphaned, or lost without a parent.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I am pretty sure that the point of the FK in the first place is to make sure that you do it right. I don't suggest removing the constraint. Now if you feel it is the right thing to do you can change the constraint so that it will delete the children that are referencing the parents you are trying to delete. But given this is mostly a group of DBA's that you are posting to, you aren't going to get a lot of comfort because the FK's are making it hard.
FYI, the children might also have their own children, so yes it will get quite hard.
-
Mostly DBAs? You're kidding. The people here are too knowledgeable and helpful to be DBAs.
You guys have come a long way over the last, oh, ten years or so.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
So no easy solution then?
-
Matey - easy is relative.
1. What prompted the removal of the table to begin with? A business requirement? An effort to speed up processing? Redesigning the database?
2. Other than the FK reference, how would you know that what you are doing would affect anything else in the database? What if the FK didn't exist, and you suddenly orphaned thousands of records and broke the database? How easy would it then be to 'fix' the database?
3. If the children and subsequent children are truly not being used in the system, then it's appropriate to delete ALL of them. If they are still being used, but pointing to the wrong FK, then by repointing them, you will both preserve integrity and simplify your task. Consider it housekeeping.
(AKA, no job is complete until the paperwork is done)
-
Not all of us wearing DBA hats have always been DBAs.
-
Well kinda...
Change the FK's to "CASCADE ON DELETE" and then you only need to delete from one table
Lookup "cascading referential integrity constraints" in Books Online for the correct syntax and to understand the possible ramifications of cascading deletes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks guys I will give all your suggestions a go. To answer the other question as to why the table needs to be dropped, it's simply because the data in the table is no longer required and an updated spreadsheet was created and required to be loaded which brought about the issues when I attempted to delete the old and load the new with or without dropping the table.
-
I guess I don't understand why anyone would apply foreign keys to such disposable data
Should be the other way around... 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
If the data in the table is TRUELY NOT NEEDED. Then there should be no harm in removing the FKeys that point to the table and then drop it.
However I suspect that in a few weeks we will be seeing a post about how to re-establish the relationships Suggestion. Remove the Fkeys, and re-name the table. See if it breaks anything. (I suggest doing this in a test environment first). -
Thanks guys all your comments much appreciated. What I decided to do is revert to a backup and then populate the tables accordingly and then upgrade the database to required version, in that way i will not need to populate the table anymore and subsequent uploads will ideally be done via the frontend. Thanks and hopefully this topic will not be brought up again.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply