Foreign Keys, Part 1 - SQL School Video

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Foreign Keys, Part 1 - SQL School Video

Foreign Keys, Part 1 - SQL School Video

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Monday, June 29, 2009 6:43 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the item Foreign Keys, Part 1 - SQL School Video

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #743576

Half Bubble

Posted Tuesday, July 21, 2009 8:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, July 06, 2012 7:03 AM
Points: 78, Visits: 168

Do you then build in index on the "foreign key" column, or does the foreign key constraint take care of that?

Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.

Post #756601

bkirk

Posted Tuesday, July 21, 2009 5:59 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 03, 2011 5:17 AM
Points: 92, Visits: 346

Nice Video and a good intro to foreign keys.

My interest in FK's are more specifically related to the deletion of data between joined tables.

Eg.

Table 1 - Orders
Table 2 - OrdersDetails

(FK most likely would be the OrderID i assume?)

A new follow up video would be very helpful to show how the deletion of status changes of this type of data would work?

If you deleted an Order from the Orders primary table, do FK's automatically delete the corresponding rows of data? Or does an error occur and stop the deletion of the primary record from the Orders table?

I guess i am looking for the best method to adopt when updating data between multiples tables.

Post #757084

Half Bubble

Posted Wednesday, July 22, 2009 7:01 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, July 06, 2012 7:03 AM
Points: 78, Visits: 168

For that, I make the OrderID an internal column the users never see. This way, they'll never update it. Then, for the foreign key, specify Enforce Foreign Key Constraints and Cascade for the delete rule.

This way, the database will forbid an Order Detail that's not connected to an Order, and if an order is deleted, the details will go away too.

Sometimes, you want to restrict a delete, like, don't delete a customer that has orders, but the foreign key constraint doesn't do that for you.

Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.

Post #757388

« Prev Topic | Next Topic »

Permissions