Post reply

Foreign Key Problem

  • December 29, 2010 at 9:28 am

    #1267271

    DBA Rafi (12/28/2010)

    Yes. I agree that this cannot be 100% achievable by SQL as this depends on the code from C# or other which is out of SQL scope.

    All those problems should be caught during testing in UAT environment.

    I think the root issue is related to version control and DDL/Code promotion procedures.

    If you do not have control of what goes into production - better to look for another job or be prepared to have a miserable life 🙂

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • December 29, 2010 at 9:57 am

    #1267283

    DBA Rafi (12/28/2010)

    Thanks for your detailed answers.

    As you said, If such warning is not available in SQL Server, then It's fine. No Issues. I should go with that.

    But I don't agree that this cannot be achievable by SQL Server.

    As a developer, if I could identify the problem just by seeing/searching the code (as you said) itself even without any testing, why can't the SQL? As this involves some syntactical and machine-achievable-logical verification and not the human logical analysis.

    It's the matter of verifying logic like "Deleting/Updating main table" Vs "FK without DELETE/UPDATE Rules"

    Thanks for your patience.

    From what's been described, there is nothing unusual about the requirements of this database. It seems that the DBA just needs to create a data model and relationship diagram document, keep it updated and accessible to all the team members, and also make sure the developers are assigned specific tasks from a project plan.

    Coming from a healthcare and banking background, I actually rarely every code a DELETE statement in any stored procedure. If I see a DELETE statement, then it makes me start thinking there is something wrong with the design of the application. Once a patient or account has been entered into the database, there are things like appointment schedules, physician vists, medical assessments, and billing that tie into it. From a business perspective, to delete the data is to loose information, in addition to whatever technical issues it presents.

    It also helps to have one stored procedure for each entity to insert / update and one for each to delete or deactivate it, and each procedure should have an owner. If the application developers start sprinkling INSERT, UPDATE, DELETE statements throughout their code wherever they see a need, then it leads to inconsistency and all sorts of other problems. For example, if one developer creates a new table with a dependency on other existing tables, then everyone has to be notified and the SQL behind a dozen or more different execute methods have to be retrofitted. However, if all T-SQL coding in within stored procedures, and one person owns each procedure, then only a few lines of code by a single developer need modifying, and the change is most often transparent to the application entirely.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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