Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: To Check Referential Integrity

    Your help is required.

    A Parent table has a entry say "Business group Id (1000)" in
    BusinessGroup Table.

    This entry(1000) can be referenced by so many tables as a foreign key
    using referential integrity. Say TFNumber Table, VPN Table, AC
    Table,......

    When I try to delete the entry (1000) two things can happen.
    a> If none of the table refering this entry it will be deleted.
    b> If atleast one table (TFNumber, AC, ...) is refering this entry
    then error will be issued.

    I can Schedule (eg: After 10 days) this entry (1000) for delete. So
    the system will throw an error only when it tries to delete the entry.
    Ideally I will be getting this error only after 10 days.

    The requirement is, even in case of Schedule the system should check
    if any of the entries in VPN, AC, TFNumber,.... is refering
    BussinessGroup entry (1000) if so issue an error. else go ahead and
    schdule it for delete.

    The same logic needs to be applied to all other
    Master-Slave/Parent-Child tables. I have lots of Parent-child table,
    so it is difficult to find out who are all master and who are all
    child and so on.

    Another thing is that int the above example Business group is a
    master/parent and VPN,AC,TFNUmber.. all are child. For an another
    scenario this child (VPN/TFNumber/AC/..) will be a master and it will
    be having set of other tables (Timer,AcSet,..) as a child.. The
    relation would be

    Business group ....
    | |
    | |
    ---------------- -----
    | | | |.. | | |..
    TFN AC VPN .. ......
    | | | |
    ------- ..........
    | | |
    Tim AcS ..

    So I would like to know using an Referential Integrity is there a way
    to find out "Are there any child refering this parent(1000)"?. So that
    I will apply this logic to all the parent/MAster.

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    > Are there any child refering this parent(1000)"?.
    The only way I know is to make a select.
    Maybe this Sybook will help you:

    "Using Triggers to Maintain Referential Integrity"
    http://manuals.sybase.com/onlinebook...TextView/48327

    Transact-SQL User's Guide
    http://manuals.sybase.com/onlinebook...sg1250e/sqlug/

    I think that you will check the referential integrity more easy with triggers, that can give you more information when you delete or update a row.
    Bye bye

    Sebastian

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Thumbs up

    Originally posted by svelasco
    > Are there any child refering this parent(1000)"?.
    The only way I know is to make a select.
    Maybe this Sybook will help you:

    "Using Triggers to Maintain Referential Integrity"
    http://manuals.sybase.com/onlinebook...TextView/48327

    Transact-SQL User's Guide
    http://manuals.sybase.com/onlinebook...sg1250e/sqlug/

    I think that you will check the referential integrity more easy with triggers, that can give you more information when you delete or update a row.
    Bye bye

    Sebastian



    Triggers can have effect when a user attempts to "modify data with an insert, delete, or update" command. Here the mentioned scenario does not do any of the above mentioned operation.

    So I would like to know is there any other way, may be using some of the sybase system table!!!! to find/check out the referential integrity ??

  4. #4
    Join Date
    Jan 2004
    Posts
    19
    you can use sysreferences and sysobjects(sysstat2 & 2 >0 if it has foreign key,sysstat2 & 1>0 if it is being referenced) for finding the dependency of the table.

Posting Permissions

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