Holy Foreign Keys and Indexes

This article shows an interesting issue with foreign keys and how they are aligned with the base tables in SQL 2005 SP 4.

SQL Server Foreign Key Update and Delete Rules

Foreign key constraints are an integral part of SQL Server database design. These are used to maintain integrity among related data in different tables. While implementing update and delete operations on values in the parent table (referenced table with primary key) we have to consider the impact on related values in the child table. SQL Server provides different rules for managing the effect of updates and deletes on child table values. How can these rules be used effectively without threatening the relational integrity?

Columns Explorer - With constraints create / drop scripts (no cursors)

Gets all columns infos, foreign keys (multicolumn fk handled), referenced primary keys, default and check constraints, indexes, Create/Drop scripts for all constraints.

Changing Foreign key names to standard naming convention across the DB

Rename the Foreign keys to standard notation - FK_childTable_ParentTable

Declarative Data Integrity

Show Cascade Delete Tree

See indented tree of tables involved in foreign key cascade deletes.

sp_truncate

Truncates referenced tables

Truncate Referenced Table

Often during the development phase one is required to truncate a table. often the task of removing foreign key references is necessary to truncate. this sp takes the pain out of it.

Disable Foreign keys

Script to disable foreign keys (SQL2005)

Find Foreign Keys

This script lists all foreign keys with parent and child table information