This discussion is archived
6 Replies Latest reply: Aug 13, 2013 1:19 AM by Philip Stoyanov RSS

Problems Engineering Surrogate Primary Key with Unique Key

marcusafs Journeyer
Currently Being Moderated

SDDM 3.3.0.747 with 2 problems (at least so far).  I am hoping that the problem is with this SDDM rookie and I have overlooked some setting. PROBLEM 1 I don’t want to start a religious debate about surrogate vs. natural keys but I am having a problem engineering both correctly from the logical model.  I am a rookie when it comes to SDDM but have many years of experience with Designer. By default I like to have both a natural UID  (UK) and a surrogate key based primary UID (PK) which is used for foreign keys.  The problem I am having with engineering is I can successfully engineer the surrogate PK’s, engineer the FK’s using the PK’s but cannot get the unique key to contain the surrogate keys in the child table.  If I check the identifying property in the relations, the PK columns and the UK columns are included in the child PK and the UK contains no columns. The Setup I have defined two reference entities, PROBABILITY and SEVERITY with natural unique keys defined.  I also have a child entity RISK_ASSESMENT with relationships back to the PROBABILITY and SEVERITY entities and both have the “Use surrogate keys:”: check box checked.  The unique key for the RISK_ASSESMENT entity includes the relationships back to PROBILITY and SEVERITY.  None of the entities have a PK or surrogate key defined and they all have the “Create Surrogate Key” check box checked.  In addition the following preferences are set: Data Modeler/Model/Logical   NOT Checked - Use And Set First Unique Key As Primary Key   NOT Checked – Name – Keep as the name of the Originating attribute   Checked – Entity Create Surrogate Key   Checked – Relationship Use Surrogate Key PROBLEM 2 When the foreign key columns are engineered I want the names to have a prefix “FK_” but they don’t.  Templates are set as follows: Data Modeler/Naming Standard/Templates   Foreign Key:  FK_{child}{parent}   Column Foreign Key:  FK_{ref column} Engineer to Relational Model/General Options   Checked - Apply name translation Marcus Bacon

  • 1. Re: Problems Engineering Surrogate Primary Key with Unique Key
    marcusafs Journeyer
    Currently Being Moderated

    Sorry for the bad formatting on the original post.  I cut and pasted and had to paste plain text so all formatting was lost.

     

    One bit of clarification.

     

    I can successfully engineer the surrogate PK’s, engineer the FK’s using the PK’s but cannot get the unique key to contain the surrogate keys in the child table.


    should have read "cannot get the unique key to contain the surrogate key FK columns from the relationships"

  • 2. Re: Problems Engineering Surrogate Primary Key with Unique Key
    Philip Stoyanov Guru
    Currently Being Moderated

    Hi Marcus,

     

    you can try DM 4.0 EA1 - problems should be solved there. About naming - you always can apply naming standards using "Apply Naming standards to keys and constraints" wizard available in context menu for relational model in browser.

     

    Philip

  • 3. Re: Problems Engineering Surrogate Primary Key with Unique Key
    marcusafs Journeyer
    Currently Being Moderated

    I have been switching between SD 4 EA1 and SDDM 3.3 trying to get things to work and trying out the template table for adding audit columns (really nice!).

     

    Concerning Problem1.  No matter what settings I use and whether I use SDDM 3.3 or SDI cannot get the FK columns to be included in the UK even though the relations are included in the UID in the entitty.  When I open the properties of the child table and click on the naming standards button and click ok it complains that the UK is not complete.  I add the FK columns to the UK and all is well including the naming standards.

     

    Concerning Problem 2.  Sometimes it engineers the names for FK's from the template and sometimes it doesn't.  Didn't see a pattern.  Gave up trying and used Naming Standards button.  I still had to change a few.

     

    The good new is, that after make changes needed in UK's and Column names of 18 tables, I know have everything deployed to Test except FK Indexes.  I think I have to do those by hand.

     

    Marcus Bacon

  • 4. Re: Problems Engineering Surrogate Primary Key with Unique Key
    Philip Stoyanov Guru
    Currently Being Moderated

    No matter what settings I use and whether I use SDDM 3.3 or SDI cannot get the FK columns to be included in the UK even though the relations are included in the UID in the entity

    DM 3.3 doesn't retain relationships in UK in case they are set to use surrogate key. So to get it working you need to put them again in UK and then engineering should work properly.

     

    I know have everything deployed to Test except FK Indexes.  I think I have to do those by hand.

    There is a script that can do it for you - at least you can try it and change the result if don't like it. If you don't need those indexes as real objects in the model then you can select to be generated automatically in DDL

     

    Philip

  • 5. Re: Problems Engineering Surrogate Primary Key with Unique Key
    marcusafs Journeyer
    Currently Being Moderated

    I did notice that sometimes the relationships were gone.  I engineered the tables, went back to the entity and the relations were still in the UK.  I once again engineered without deleting anything and still no FK's in UK of the child.

     

    Thanks for the tip on DDL generating indexes.  Having them in the model is not important as long as they are generated for deployment.  Found the preference and set it.

  • 6. Re: Problems Engineering Surrogate Primary Key with Unique Key
    Philip Stoyanov Guru
    Currently Being Moderated

    I once again engineered without deleting anything and still no FK's in UK of the child.

    There is a problem in synchronization in DM 4.0 EA1. This will be fixed for production release.

     

    Philip

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points