Tables with Foreign key reference

Question

Tables with Foreign key reference

matey

SSCommitted

Points: 1845
More actions
July 31, 2007 at 3:45 am

#69661

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

matey SSCommitted Points: 1845 More actions · Answer 2

unfortunately there's more than one reference table, so deleting from the child tables will take a long time.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

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.

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

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.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

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

matey SSCommitted Points: 1845 More actions · Answer 6

matey

SSCommitted

Points: 1845

August 3, 2007 at 7:44 am

#724505

So no easy solution then?

steve smith-401573 SSChampion Points: 10889 More actions · Answer 7

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)

steve smith-401573 SSChampion Points: 10889 More actions · Answer 8

steve smith-401573

SSChampion

Points: 10889

August 3, 2007 at 12:50 pm

#724616

Not all of us wearing DBA hats have always been DBAs.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

matey SSCommitted Points: 1845 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

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)

Bob Fazio SSChampion Points: 10794 More actions · Answer 12

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).

matey SSCommitted Points: 1845 More actions · Answer 13

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.