SQL Table Primary and Foreign Keys

Question

SQL Table Primary and Foreign Keys

bkirk

SSCrazy

Points: 2824
More actions
May 31, 2009 at 8:29 pm

#134477

I am new to using Primary Keys and Foreign Keys.
I am looking to develop an website administration backend and was wondering the best way to deal with 'Deleting' Sale Orders and Order details from two SQL tables flagged with primary and foriegn keys for this kind of relational data.
Here is an example of what i mean:
Table 1 = Ordersummary (PK = OrderID)
Table 2 = Orderdetails (PK=OrderdetailsID, FK=OrderID)
How would i go about deleting an entire order?
If i delete the ordersummary record will it automatically delete the orderdetails records attached to that OrderID? Or vice-versa?
Or will it simply error due to the foreign key relationship?
Apart from turning off the foreign keys and running 2 delete statements inside a loop, i am looking for some guidance to keep the foreign key relationships and still perform an effecient order deletion.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Randolph West SSCommitted Points: 1585 More actions · Answer 1

If you have set up cascading deletes (*shudder*), you can just delete the summary record, and the detail will go as well.

I never turn on cascading deletes myself, so I'd delete the detail (or set the records inactive if there's such a field in the table - I also don't like deleting any data), then delete the parent (or set inactive). It won't affect your relationship if there are no other dependencies.

That's the short answer. I'm assuming you've taken into account the impact of deleting order information from your database, have appropriate backups, etc.

R

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 2

bkirk (5/31/2009)
Apart from turning off the foreign keys and running 2 delete statements inside a loop, i am looking for some guidance to keep the foreign key relationships and still perform an effecient order deletion.

You wouldn't have to "turn off FK's" to delete. You would need to delete all child records first then the parent record. Use a stored procedure and put the logic in there.

bkirk SSCrazy Points: 2824 More actions · Answer 3

Thanks for both posts.

So if i understand this correctly:

1) I can still keep the foreign key relationship on my orderdetails table?

2) If deleting an order, i would have to first have to delete the records from the orderdetails (FK), and then go back to the ordersummary (PK) and delete the primary record? In other words always delete FK's before PK's?

3) What is the point or benefit of having a PK, FK relationship? Is it simply to stop the deletion of Primary records when foreign records exist?

I was acutally hoping for a single delete statement that would wipe out the related data in both tables. Sounds like i cannot do that and will need to create a SP using 2 SQL statements with the PK (OrderID)?

Panks-913490 SSCrazy Points: 2131 More actions · Answer 4

Answers to your questions below:

1)Yes

2)If you use On Delete Cascade on Orderdetails, any order deletion

from Ordersummary would result in deletion from Orderdetails.

3)Its primarily used for referential integrity i.e. to avoid invalid values to be entered in a particular column. This is the reason deletion from parent is restricted when child records exist. In your case, you wont want any orderid in orderdetails which does not exist in Ordersummary.

You are right, a single delete can not be used here. First delete the child records, then the parent records else use the 'on delete cascade'.

But i strongly agree with Randolph's post that you should not delete records rather make them inactive. Create a colmn Active in the tables and update active = 'N' to inactivate records.

Thanks

Pankaj

bkirk SSCrazy Points: 2824 More actions · Answer 5

Thanks for the excellent responses. I agree the deletion of data is not the best solution. Status updates are always the better way to go.

I will look at the cascade SQL option to see if it is relevant for any other PK, FK relationships on other table data that is less critical as orders.

I can already think of another table structure where it might come in handy for mailing members and newsletter subscriptions. However, then again the 'opt out' status might also be the way to go rather than full deletion of records.

Thanks again.