Model: Use referential integrity (Oracle)

Author: juerg.fross@teamspirit.ch (jfro)

We have the idea to make Uniface create a script for referential integrity to use in Oracle.

We have some troubles in doing this for "one to many" relationships. Does somebody use this?

I found in the help under "Use Referential integrity:"
"The number of fields in the key of the one entity must be equal to the number of foreign key fields specified in the relationship."

Maybe I don't understand this correct - but this would in my opinion mean that never a "one to many" relationship will be valid. In the "many-entity" I will surely always have an attribute more in the key as in the "One entity".

Thanks in advance!
Jürg

8 Comments

  1. Hi,

    In the Uniface Application Model you have could have two entities: DEPARTMENT and EMPLOYEE.

    The primary key of DEPARTMENT is DIVISIONNR and DEPARTMENTNR
    The primary key of EMPLOYEE is EMPLOYEENR

    To create a one to many relationship between DEPARTMENT and EMPLOYEE you need to:

    1 - Make sure EMPLOYEE also has fields for DIVISIONNR and DEPARTMENTNR
    2 - Create a relationship in the model between DIVISIONNR and DEPARTMENTNR of DEPARTMENT on the one side and DIVISIONNR and DEPARTMENTNR of EMPLOYEE on the many side.
    3 - And it is not uncommon to put a foreign key on DIVISIONNR and DEPARTMENTNR of EMPLOYEE

    This is "the same number of fields" that is meant.

    What you say would be a problem if both PRIMARY keys needed to have the same number of fields.

    Regards,

    Theo Neeskens


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  2. Hi,

    Thank you for the fast reply. This is I think exactly what we do...

    I did now another try and a script was created...

    BUT:

    • When creating the script I receive a message "6910 - Error while creating SQL. See message frame".
    • When I check the message frame there is nothing in it - respectively the last thing which was written in when compiling a form.
    • The script for the referential integrity just doesn't contain all the scripts I defined in the model. Which one is missing or not I cannot see.

    Thanks for any help and tips...

    Jürg

     

     


    Author: jfro (juerg.fross@teamspirit.ch)
  3. A typical error, which also could happen in export and other areas of uniface:

    If you enter a file name + location with an extension which is redirected by asn, this or a similar message appears, no message text and no file is created.

    If your file name was C:\location\filename.sql

    Try this:

    (1) Remove any redirection  *.sql    from your asn file (and restart idf)

    or

    (2) enter the name without path information  (filename.sql)
    Then it is stored at the place where asn direct the file to.

    or

    (3) use another extension, which is not redirected by the asn file (  C:\location\filename.sql123 )

    Your're right, compuware should write a bit more information into the message frame in case of this error ... (!)


    Author: gypsilon (wva@gypsilon.de)
  4. On top of what gypsilon says you also need to make sure that:
    - The location you are pointing to really exists
    - You have write permission there
    (I am stupid I make mistakes like that)


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  5. Hi,

    just a quick suggestion... I believe you have to analyze model... if you want these create script utilities to work. So... have you analyzed the model?

    Zdenek


    Author: sochaz (zdenek.socha@fullsys.cz)
  6. Hi all,

    To help you check referential integrity as for other questions of data modeling, SILVERRUN , Grandite's professional data modeling tool, supports a bidirectional interface with Uniface.

    SILVERRUN can e.g.

    • open a Uniface model
    • create graphical representations of a Uniface model
    • check its integrity
    • validate it for a target system (e.g. Oracle)
    • generate an SQL script for the target system.

    On the complementary, SILVERRUN can e.g.

    • reverse engineer a database (e.g. Oracle)
    • create a graphical representation of the database
    • help to refine / further develop the related data model
    • create a Uniface model from a data model.

    If you need any additional information, please go to http://www.silverrun.com, contact us at  info@silverrun.com or simply reply to this post.

     

    Regards,

    Grandite

    Customer Service


    Author: None (None)

  7. Hi all,

    To help you check referential integrity as for other questions of data modeling, SILVERRUN , Grandite's professional data modeling tool, supports a bidirectional interface with Uniface.

    SILVERRUN can e.g.

    open a Uniface model
    create graphical representations of a Uniface model
    check its integrity
    validate it for a target system (e.g. Oracle)
    generate an SQL script for the target system.

    On the complementary, SILVERRUN can e.g.

    reverse engineer a database (e.g. Oracle)
    create a graphical representation of the database
    help to refine / further develop the related data model
    create a Uniface model from a data model.

    If you need any additional information, please go to http://www.silverrun.com, contact us at info@silverrun.com or simply reply to this post.



    Regards,

    Grandite

    Customer Service


    Just kidding, it is nice that there are tools that can interface with Uniface. The text in this post probably just does not show the benefits of this product very well:

    Hi all,

    To help you check referential integrity as for other questions of data modeling, IDF , Uniface's development tool can e.g.

    open a Uniface model (Model Editor)
    create graphical representations of a Uniface model (Graphical Model Editor)
    check its integrity (Analyze Model)
    validate it for a target system (e.g. Oracle) (don't know what this means)
    generate an SQL script for the target system. (Create Table Scripts)

    On the complementary, IDF can e.g.

    reverse engineer a database (e.g. Oracle) (Import Definitions)
    create a graphical representation of the database (We can't do that but Model and DB should be 1-on-1)
    help to refine / further develop the related data model (Use the Model Editor to add indexes and field constraints)
    create a Uniface model from a data model.(Import Definitions)

    If you need any additional information, please go to http://www.uniface.com, or simply reply to this post.



    Regards,

    Theo Neeskens



    Author: Theo Neeskens (tneeskens@itblockz.nl)
  8. HI all

    Thank you for all the tips!

    I think we have another problem. It seems something in the data model is corrupt and this results in getting the message "6910 - Error while generating SQL: see message frame". Unfortunately in the message frame is nothing. What really all is corrupt we didn't find out yet.. We hope this message will disappear once we found all corrupt things.

    We had different Referential integrities which haven't been included in the script we generated. After identifiying the missing ones we could up to now solve the problem like this:

    1. Duplicate the concerned entity and give it a new temporary name
    2. Delete the concerned entity. As Uniface seems to have troubles (bug) in deleting entities maybe first delete the relastionships. Check if there are inter-model-relationships which hav to be deleted also (another bug).
    3. Exit Uniface - as it seems to have a problem after deleting and trying to reuse the same name (again a bug).
    4. Enter again in Uniface and renaming the entity with the temporary name back to the original name.
    5. Deleting temporary entity (again first deleting the relationships)
    As we have more than 500 relationships it will take some time to check all. A hint to the real problem or problems instead of this "error-message" and nothing else would be very helpful.
     
    Jürg
     
     

    Author: jfro (juerg.fross@teamspirit.ch)