Adding Disabled Foreign-Key Constraints for Documentation Purposes

  • I have a active, highly relational, OLTP database with about 750 tables. There are almost no foreign-key constraints defined in the dB, the application layer keeps track of most of this. Does this result in orphaned data from time to time? Yes, but it hasn't been an issue and that's not what I'm trying to address here.

    I have recently been tasked with documenting all relationships in the dB. I would like to simply add foreign-key constraints so the dB is self-documented, but I do not want to inflict any new overhead on the database, nor do I want to find out that some parts of the app layer delete parent records before children; I merely want to document with an eye toward possibly enabling some constraints at some time in the future.

    My question is: Is adding permanently disabled foreign-key constraints a good idea for documentation purposes? I can't imagine there would be any performance impact. Is there a scenario where disabled foreign key constraints are re-enabled, implicitly (for ex: when indexes are rebuilt, etc)? Has anyone done this or know of people who have? Any general thoughts about this approach to documenting relationships?


  • I would be inclined to start by using a tool like Visio to produce an entity relationship diagram. If the system is provided by a third party the suppliers may be able to provide you with one although you will need to check with them if they are prepared to support their system when it has FKs. If the system is in-house you could start by asking the developers for the class diagrams.

    Before applying FKs you should capture all insert, update and delete queries to check the order tables are updated. As adding FKs will produce an overhead you should check the effect in a test environment using something like SQL Server Distributed Replay. ( SQL Server Distributed Replay is fiddly and it is difficult to get a completely accurate representation of a production workload but it should give a reasonable idea of the effects of changes.)



Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply