Results 1 to 2 of 2

Thread: Database Rework - Primary Key and Foreign Key Approach like SQL

  1. #1
    Member
    Real Name
    Bruce Roberson
    Join Date
    Mar 2011
    Posts
    80

    Default Database Rework - Primary Key and Foreign Key Approach like SQL

    There is probably a good approach to this but I need some help. For many years, I have maintained sets in Alpha 5 linking them on a "set of fields" rather than just using the SQL approach of using a primary key as an incremental value, and then linking child tables to that table using the primary key as the foreign key in a child file. I want like to give this approach a try now working with my existing table structure.

    My parent table contains roughly 1750 records. My first child has over 2000 records and there is a one to many relationship currently. Obviously many parent records only have 1 child in this example. They parent and child each have a field in their tables in the database that gets maintained with field rules: (UT(NEW_NAME)+"-"+UT(PROD_CODE)). I enter New_Name and Prod_Code in a form in the parent.

    All I did so far is to create a new field called ID in the parent table and I set it to a number and caused it to auto increment. I haven't done anything in my child table yet. So, I believe I need to do an update to assign ID numbers to my ID field. Then, the trick would be to create a foreign key field in the child, and insert the parent's ID as a foreign key in the child table. To this, I would anticipate using the linking methodology described above.

    This is just one example of what I would need to do at this point. Once I understand the process, I hopefully can duplicate it in other cases for the same database.

    Thanks in advance for getting me started.


    Bruce

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,100

    Default Re: Database Rework - Primary Key and Foreign Key Approach like SQL

    As you have done the incementation in the Parent, the update should be easy in the Child.
    Where new name and prod code are the same in both, update FK to the PK found in the Parent.
    Then, build a new set with the link being the PK.

    I would try this on a non production data set first.

    Edit.

    You might have a few issues with using an incremented field (the PK) in an update using Field Rules Reevaluate, so suggest you make add another field, which is a Calculated Filed, based on the Incremented PK field. I have tested that approach and it works fine.
    Last edited by Ted Giles; 12-06-2018 at 05:43 AM.
    Ted Giles
    Example Consulting - UK
    .

    https://exampleltd.example-software.com/
    See our site for Alpha Support, Conversion and Upgrade.

Similar Threads

  1. Primary & Foreign keys in My SQL
    By Steviev in forum Mobile & Browser Applications
    Replies: 4
    Last Post: 06-23-2014, 05:55 PM
  2. Replies: 0
    Last Post: 09-19-2013, 04:34 AM
  3. Primary, Foreign & Natural Keys
    By MoGrace in forum Alpha Five Version 8
    Replies: 8
    Last Post: 05-16-2008, 10:52 AM
  4. Rework fields in a form
    By edward.mattison@scbhn.org in forum Alpha Five Version 5
    Replies: 2
    Last Post: 12-05-2003, 12:29 PM
  5. Approach database to Alpha database
    By diane novatnik in forum Alpha Five Version 5
    Replies: 4
    Last Post: 07-31-2003, 09:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •