Advanced Foreign Key Configuration

Most of us apply the default configuration for foreign keys, but did you know that it supports a rich set of options include the ability to cascade a delete? Often enabling the correct foreign key configuration can save a lot of coding that would extensive testing.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Will Casey on 10/14/2009

very clear and concise

Thad Moorer on 10/14/2009

Excellent advanced followup to foriegn key initial videos. Also provides some insight into how to use the concept in place of some of the practices, such as coding and triggers, in order to make your solutions more efficient and effective.

Don Weigend on 10/14/2009

Very good explanation

C G on 10/14/2009

Clear example showing powerful feature.

luther smith on 10/14/2009

Good info

Tonci on 10/14/2009

Great work in showing a clear alternative to triggers. Constraints might be willing to be used instead of triggers, if for example, you are using replication and don't want to have your own triggers.

borjonx on 10/14/2009

excellent, plain, simple example. clearly spoken word - very nice!

sunil on 10/14/2009

Good One

F6F46BB996 on 10/15/2009

Thank you and keep creating more!

Travis on 10/15/2009

Caution! Use Cascading Updates on FK's at your own risk.. I've seen lazy DBA's use them and really screw table(s) up bad!!!! You better know what you are doing before using the CASCADE option in a foreign key...

Emine Dekkar on 10/17/2009

did not know about the use of defaultfor all fk while deleting the pk...nice feature for BI ..thx

Cheryl Pacenka on 10/19/2009

very good

Murali on 10/20/2009

good

Steve Culshaw on 10/23/2009

Excellent tip

gicu on 10/26/2009

Love this! :)

Susan Lammi on 10/30/2009

great practical explaination.

Steve Harris on 11/18/2009

excellent presentation

346FAF6933 on 1/12/2010

Excellent content. I've been working with SQL Server for a number of years and never new of this feature.

yeskay on 2/5/2010

Excellent .. clear concise explanation. thanks a lot...

sudipta on 4/16/2010

Not so good !

Nate on 12/14/2010

Great video!!! Always good to get back to the basics.

Boyd Petrie on 8/23/2011

It's worth stressing that using the "CASCADE" is a very risky option for the DELETE option. UPDATE is a bit less risky, but make sure you understand the consequences of the CASCADE before implementing it. Frequently, this is done by DBAs who want to maintain data integrity without realizing what that action will do. When I first began working with an RDBMS, I applied foreign keys and cascading updates/deletes. These problems tend to pop up on data requiring a level of denormalization (such as a customer's address at the time of an order; the customer's address may change in the future and should not be retroactively applied to a previous order). Cascading an update of an address to a customer's order could be disastrous (or that could be the intended action).