Alpha Video Training
Results 1 to 14 of 14

Thread: Referential integrity quandry

  1. #1
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default Referential integrity quandry

    I have a set defined in A5 containing 3 tables of customer, invoice and invoice item as per the following example:

    A. customer -- 1 to many --" invoice key is CustomerNo
    B. invoice -- 1 to many --" invoice item key is CustomerNo + InvoiceNo

    Of course when I create a new invoice item via this set I want the customer number and invoice numbers automatically maintained for me in all relevant tables. But wait, A5 has referential integrity. Great! This is why I love A5 - it does all these housekeeping things automatically for me.

    So in my A5 set definition, my table link in A. is CustomerNo of course, and my link in B. is an expression: str(CustomerNo,6,0) + InvoiceNo.

    But A5 throws up an error message if I try and select "Cascade changes/deletes" saying " ... the parent key cannot be an expression". Duh! Stymied!

    Does anyone know how I can get A5 to enforce referential integrity in this case? Many thanks.

  2. #2
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default RE: Referential integrity quandry

    I would think the Invoice -" Invoice_Item link should be based only on the invoice number. I see no advantage to including the customer number in the link definition. After all, can one invoice go to multiple customers?

    Also, when building invoices, I recommend linking Invoice_Head to the Customer table and Invoice_Head to the Invoice_Items table. (i.e., don't start from the customer table - think of it as, "I'm building invoices not customers.") Then, if you want to build a report based on customers, filter the Invoice_Head table by the customer number.

  3. #3
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    Thanks, Cal. That's most helpful.

    Unfortunately in the example I used in an attempt to simplify things, I forgot about one thing. In my application the invoice numbers are assigned starting at 1 for each customer. So the number is not unique across all customers. Darn nuisance too. 8-(

    I have tried several times now to devise a suitable form that shows:

    A. a customer,
    B. all invoices for that customer in a browse,
    C. all invoice line items for a customer invoice in a second browse - just click on an invoice in B.

    Now to add a new invoice line item I just have to click on a new entry in C and ... oops, referential integrity problem because I need both the customer number and invoice number as a unique identifier to link the line item to the customer invoice.

    So I cannot use an expression in my set (customer number plus invoice number) linking invoices to invoice items because A5 does not allow expressions when defining a referential integrity rule.

    Also, I cannot use a single key (customer number) as the linking field plus a filter (on the invoice number) because A5 won't allow a child filter expression (invoice number) in a set to reference a field from the parent (invoice) table. And even if it did allow this I still would not get referential integrity applying to the customer number if I add a new invoice line item.

    It would be sooo much easier if A5 allowed referential integrity to apply to all fields in a link expression. It actually allows only one. 8-(

    I hope the good folk at Alpha Software enhance this sometime soon.

  4. #4
    Member John Gamble's Avatar
    Real Name
    John Gamble
    Join Date
    Jan 2001
    Location
    Newmarket, ON Canada
    Posts
    886

    Default RE: Referential integrity quandry

    Brett

    Try using a key which is calculated by concatinating the fields you require Key = "Customer + InvoiceNo". I agree, single field RI sucks.

    I use the above method all the time

    John

  5. #5
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    That's interesting, John. I found one of my A5 applications where I had two numeric keys joining two tables in a set where I had defined the 1:1 linking expression as 10000*key1 + key2 just so that the result was still unique. A5 allowed me to apply referential integrity to this - well at least it did as far as I know because there is no error message.

    However, if I try and concatenate char fields in a similar fashion with referential integrity applied, I get the error message "You can only select the 'Cascade changes/deletes' option if the parent key is a field, not an expression". As I said before, duh.

    What I would like to know is:

    Q1: Are there any circumstances under which I can use more than one field to define a link between two tables in a set and apply referential integrity to this link. If so, what are these circumstances?

    Q2: Is the answer to Q1 affected by whether or not the linking fields are numeric or char?

    Q3: Is the answer to Q1 affected by whether or not the relationship between the two tables is 1 to 1 or 1 to many?

    Hopefully someone who knows can clarify this for us.

  6. #6
    Member John Gamble's Avatar
    Real Name
    John Gamble
    Join Date
    Jan 2001
    Location
    Newmarket, ON Canada
    Posts
    886

    Default RE: Referential integrity quandry

    Brett

    Sorry I should have been more specific. In the primary table create a calculated table field, KEY_1 = FIELD_1 + FIELD_2 + Field_3......
    e.g.
    Field_1 Sysno C-10
    Field_2 Floor C-2
    Field_3 Door N-3

    Calculated field SYS_FLR_DO = "Sysno+Floor+str(Door,3)
    This is now your linking key.(Mixed cjar. & numeric)

    In the secondary table define your key as SYS_FLR_DO (User input. Define the individual fields as follows;

    Field_1 = Calculated LEFT(SYS_FLR_DOO,10)
    Field_2 = Caclclated RIGHT(LEFT(SYS_FLR_DOO,12),2)
    Field_3 = Calculated VAL(UT(RIGHT(SYS_FLR_DOO,3)))

    The set linking will pass SYS_FLR_DO to the secondary table & your calculated table fields will extract the individual fields.

    RI will now operate on the single key "SYS_FLR_DO"

    Hope this clarifies the confusion.

    John

  7. #7
    Member John Gamble's Avatar
    Real Name
    John Gamble
    Join Date
    Jan 2001
    Location
    Newmarket, ON Canada
    Posts
    886

    Default RE: Referential integrity quandry

    Brett

    Just a further note. This is a workaround and works perfectly. The downside is that if you change a field size, you have to adjust your entire application. Not proper database design!

    I have discussed this with Alpha, but it certainly would'nt hurt for other users to also make the request. If enough users indicate this as a priority , maybe its priority will percolate up.

    John

  8. #8
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    Thanks, John. Good suggestions, I certainly try them out.

    On reflection I now think A5's inability to provide referential integrity when there are multiple keys in the linking field between tables is a MAJOR shortcoming. I have therefore posted in the Features Wish list forum (link below) a suggestion that this feature be provided. Hopefully, this will receive some attention because it will save a lot of work for serious A5 developers - those who develop comprehensive business applications.

    Thanks again.

    http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=17&id=783&loc=0&offset=0&sortby=lastreply&direction=desc&thread=783

  9. #9
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    Ah, yes. I see how it works. Very clever. 8-)

    Another downside is that the child table is now made difficult to update directly because the 3 "real" fields are now calculated fields. So a form that updates these 3 fields has to break up sys_floor_do (15 characters) into its individual 3 component fields so that the individual fields can then be updated by the field rules. Too bad if we want to apply individual field rules to these 3 fields. Very messy. (I hope I have explained this clearly).

    All this palaver is necessary because field rules apply globally to the tables on which they are defined and cannot be applied just to the set as we could in Alpha Four. But that's another story.

    I would much prefer to see Alpha Five enhanced to allow referential integrity to apply to all the fields in a set link and not be limited to just one field.

  10. #10
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,960

    Default RE: Referential integrity quandry

    if you made the invoice number itself the customer number plus the invoice number it would greatly simplify things.

  11. #11
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default RE: Referential integrity quandry

    Brett:
    Your quandry revolves around one simple issue:
    "the invoice numbers are assigned starting at 1 for each customer"
    That's a no no.

    Even if you manage to figuare out a way around RI (which is easy to do), you will have trouble, MAJOR trouble, with this design, here is why:
    Cust# 123 Inv# 12 will have the same key as:
    cust# 1231 Inv# 2
    That's a big mess! These 2 customers will bring up line items that belong to each other!
    (there is a way to get around the above, but I think you should avoid this alltogether).

    You need to make inv# sequential across all invoices, period. Then you could apply RI and avoid this mess in the future.

    As to RI in general and the fact that alpha does not allow expression, I am glad they do not and I hope they won't. Alpha is correct on not allowing that.

    Think about it:
    1-Suppose you have a parent table that have these two fields: Fname & Lname and a child table with the same fields
    2-Let's say you want to link these two tables with an expression (Fname+Lname) and add RI:
    3-The result of the expression for Fname:John & Lname:Smith is: "JohnSmith"
    4-How is alpha to break this expression down at the child level? Does it break it down to John & Smith where John goes to Fname and Smith goes to Lname? or does it break it down to Joh & nsmith? or Jo & hnsmith?
    5-You might say, well, why can't alpha maintain the original components in some holding place then it will know what to break the expression down to?
    Then it's not an expression any longer, we are back to fields.
    You might say, well why can't you use more than one field, not in an expression, just two un-adulterated fields, to link so as to be able to pass the values from these 2 fields from the parent to the child?

    Well, that's very easily done in more than one way, the easiest of which is to use an alias table linked on the other field, Vola!

    Gabe

  12. #12
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    Gabe,

    No, sorry. I was only using my example as an analogy to make things clearer. It is certainly not the way my application is designed. I am an experienced database designer so I do not (normally) fall into such traps. 8-)
    The main point I wished to convey was if I have a parent table PT linking a child table CT in a 1 to many relationship where there are two (or more for that matter) linking fields F1 and F2 that are primary keys and collectively the unique identifier for PT (F1 and F2 are each foreign keys for separate tables), how can I get A5 to enforce referential integrity between PT and CT on new records I might add to CT? These answer appears to be that I cannot because A5 only allows one such field. This is not good news. 8-(
    But your suggestion about an aliased table linked to the other field intrigues me. Do I detect an effective work-around manifesting itself here? Please tell me more about your suggestion.

  13. #13
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default RE: Referential integrity quandry

    Brett:
    Using an alias is an easy, but rather "primal" and, worse, if you have a big set to begin with, it will complicate matter severely.

    Personally, I would write a simple script that carries the values from the parent to the child, you could carry as many fields as you wish and you could do the same when you change or delete.

    Gabe

  14. #14
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Referential integrity quandry

    Thanks, Gabe. Yes I think am will have to do this and tie the script to a "save" button on the form. I can't think of any other durable solution. Just as well my database doesn't contain too many of these types of tables.

Similar Threads

  1. Referential Integrity
    By Jon Lau in forum Web Application Server v6
    Replies: 2
    Last Post: 03-30-2005, 08:46 AM
  2. Referential Integrity
    By Ray in forum Alpha Five Version 5
    Replies: 0
    Last Post: 04-09-2003, 03:32 PM
  3. referential integrity
    By Ronald Anusiewicz in forum Alpha Five Version 5
    Replies: 6
    Last Post: 03-14-2003, 03:52 AM
  4. Referential Integrity
    By John Spyrou in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-24-2002, 08:48 AM
  5. Referential Integrity
    By James Peterson in forum Alpha Five Version 4
    Replies: 3
    Last Post: 10-22-2001, 07:04 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
  •