What are some best practices with setting database foreign keys

What are some good rules of thumbs and pitfalls that database designers should watch out for when setting up foreign key constraints in a database?

Also, when inheriting an existing database with few foreign keys, how difficult is it to just add foreign keys onto an existing schema?

Rule of thumb, never set up a relational database without the foreign key relationships if you care about the integrity of your data. Yes this can slow dow data inserts/updates and deletes some. This is a good thing as it is taking actions to protect your data. Without data integrity all of the data in your database is untrustworthy and therefore useless.

It is generally not hard to set up foreign keys later (in SQL Server you run an Alter table statment to add the key), the difficulty in doing so is the reason why it is unprofessional to not add them in the design phase. If you didn't have an FK, it is highly likey that you have some data that doesn't meet the rules of the PK/FK relationship and that needs to be cleaned up first.

• Don't allow nulls in foreign keys

• Avoid using DEFERRABLE constraints even if your DBMS supports them.

• Consider adding the ON UPDATE CASCADE option if you think you must but also consider the alternatives (insert then update rather than directly update the candidate key).

• Be aware that adding a constraint is often harder than removing it. Adding constraints is probably more likely to break existing code (some DML may not work) and therefore potentially requires more development and testing time to fix. For that reason it's better to add constraints earlier rather than later in every development cycle - you can always remove them later.

• When you inherit a schema, you will have to add constraints to it. (Not just foreign key constraints.)
• Someone will have to fix bad data before you can add constraints.
• It takes people a long time to fix bad data. (They have "real" work to do.)

The first database design job I did as a professional was to replace an existing database for a Fortune 500. It was small, even for the 1980s. A couple of administrative people were assigned to fix data errors uncovered during analysis and design. They could fix a couple hundred errors a day. They didn't want all the errors at once, just a couple hundred a day.

So I fed them a couple hundred errors a day. Every day for six months.