Referential Integrity Tips

Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?

In many databases, foreign key constraints are not used initially and then later created on tables after data already exists. If data already exists and there are possible foreign key violations the constraint may be created using the NOCHECK option. In addition, sometimes when loading BULK data, foreign keys are disabled or removed to make the load faster. Because of these issues you may have orphan data in your database tables. There are ways to check this using NOT IN or NOT EXISTS queries, but in this tip we look at another method and a stored procedure built around this method to find records in violation.

One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?

I'm designing a table and I want to enforce a relationship between two tables (a parent table and a child table). Would I be better off just writing triggers to enforce the relationship or should I declare a foreign key constraint? What are the pros and cons of each approach? Can you provide some sample code to show the various issues?

SQL Server Enterprise Manager and Management Studio are pretty good tools for giving you information about a particular object, but when you want to get information across your entire database or server this becomes a bit of a challenge. There are several system stored procedures and now dynamic management views in SQL 2005 to provide some of the information, but there is still a bit of missing functionality if you want to get data across the database of server. One of these recent challenges was getting foreign key information across the entire database. Management Studio has some nice additions where you can see the FK constraints on a table by table basis, but getting all of the foreign keys is a bit more a challenge.

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 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. In this tip we look at all of the options.

Some time ago, I had a water cooler discussion with one my company’'s java developers about maintaining data integrity. His belief was that all data integrity checks should be handled in the logic tier of an n-tier system. I politely explained why I consider this a bad practice. Foreign keys are specifically provided by SQL Server to prevent your database from storing invalid data relationships and should be a mainstay in every relational database model developed!

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 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. In this tip we look at all of the options.

Some time ago, I had a water cooler discussion with one my company’'s java developers about maintaining data integrity. His belief was that all data integrity checks should be handled in the logic tier of an n-tier system. I politely explained why I consider this a bad practice. Foreign keys are specifically provided by SQL Server to prevent your database from storing invalid data relationships and should be a mainstay in every relational database model developed!

Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?

I'm designing a table and I want to enforce a relationship between two tables (a parent table and a child table). Would I be better off just writing triggers to enforce the relationship or should I declare a foreign key constraint? What are the pros and cons of each approach? Can you provide some sample code to show the various issues?

SQL Server Enterprise Manager and Management Studio are pretty good tools for giving you information about a particular object, but when you want to get information across your entire database or server this becomes a bit of a challenge. There are several system stored procedures and now dynamic management views in SQL 2005 to provide some of the information, but there is still a bit of missing functionality if you want to get data across the database of server. One of these recent challenges was getting foreign key information across the entire database. Management Studio has some nice additions where you can see the FK constraints on a table by table basis, but getting all of the foreign keys is a bit more a challenge.

In many databases, foreign key constraints are not used initially and then later created on tables after data already exists. If data already exists and there are possible foreign key violations the constraint may be created using the NOCHECK option. In addition, sometimes when loading BULK data, foreign keys are disabled or removed to make the load faster. Because of these issues you may have orphan data in your database tables. There are ways to check this using NOT IN or NOT EXISTS queries, but in this tip we look at another method and a stored procedure built around this method to find records in violation.

One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?

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 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. In this tip we look at all of the options.

In many databases, foreign key constraints are not used initially and then later created on tables after data already exists. If data already exists and there are possible foreign key violations the constraint may be created using the NOCHECK option. In addition, sometimes when loading BULK data, foreign keys are disabled or removed to make the load faster. Because of these issues you may have orphan data in your database tables. There are ways to check this using NOT IN or NOT EXISTS queries, but in this tip we look at another method and a stored procedure built around this method to find records in violation.

I'm designing a table and I want to enforce a relationship between two tables (a parent table and a child table). Would I be better off just writing triggers to enforce the relationship or should I declare a foreign key constraint? What are the pros and cons of each approach? Can you provide some sample code to show the various issues?

Foreign keys (FK) are designed to maintain referential integrity within your database. When used properly FKs allow you to be sure that your data is intact and there are no orphaned records. On the flipside of using FKs to maintain referential integrity, they also become an issue when you need to change table structures or temporarily modify data that might violate the foreign key constraint. Other tips have been written that show you how to identify your FKs and why you should use them, but what is the best approach for manipulating FK constraints to make structure or data changes?

Some time ago, I had a water cooler discussion with one my company’'s java developers about maintaining data integrity. His belief was that all data integrity checks should be handled in the logic tier of an n-tier system. I politely explained why I consider this a bad practice. Foreign keys are specifically provided by SQL Server to prevent your database from storing invalid data relationships and should be a mainstay in every relational database model developed!

One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?

SQL Server Enterprise Manager and Management Studio are pretty good tools for giving you information about a particular object, but when you want to get information across your entire database or server this becomes a bit of a challenge. There are several system stored procedures and now dynamic management views in SQL 2005 to provide some of the information, but there is still a bit of missing functionality if you want to get data across the database of server. One of these recent challenges was getting foreign key information across the entire database. Management Studio has some nice additions where you can see the FK constraints on a table by table basis, but getting all of the foreign keys is a bit more a challenge.