This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 19, 2013 10:11 PM by BillyVerreynne RSS

Contraint to Chain back a Record

olivares Newbie
Currently Being Moderated
Hello

I have a table that has 2 columns, col1 and col2. I would like to add a constraint or validation when two different values chain to each other. For example
col1   col2
1      2 
2      3
3      1
I would like to prevent from inserting a value to either column that loop/chain back to itself. Is there an algorithm that will prevent this from happening?

Thank you,
Olivares
  • 1. Re: Contraint to Chain back a Record
    jeneesh Guru
    Currently Being Moderated
    olivares wrote:
    I would like to prevent from inserting a value to either column that loop/chain back to itself. Is there an algorithm that will prevent this from happening?
    You mean,you don't want to insert (3,1) and (1,3)
    create table table1 (col1 number,col2 number);
    
    create unique index u_ndx on table1(least(col1,col2),greatest(col1,col2));
    
    insert into table1 values(1,3);
    
    1 rows inserted.
    
    insert into table1 values(3,1);
    
    SQL Error: ORA-00001: unique constraint (SCOTT.U_NDX) violated
  • 2. Re: Contraint to Chain back a Record
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Olivares,

    Sorry, no; I don't think there's any constraint or easy way to do that kind of validation.

    You could do it with a trigger. Since the trigger needs to look at other rows in the same table (let's call your table fubar), it would be easiest to replicate those 2 columns in another table (let's call it fubar_2). Before doing any INSERT or UPDATE on those columns in fubar, the trigger would do the same DML in fubar_2, and then perform a CONNECT BY query in fubar2 to see if the new row is involved in a loop. If so, it would ROLLBACK the DML in fubar_2 and raise an error; if not, it would do the same DML in fubar.
  • 3. Re: Contraint to Chain back a Record
    RajeshKanna Newbie
    Currently Being Moderated
    Hi Olivares,

    Create one trigger before insert on that table. this should not allow same values on both two columns
    please Check the below code this will help u.

    create table tab1(n1 number(5),n2 number(5));
    
    create or replace trigger trig_instab1
    before insert 
    on tab1
    for each row
    begin
    
    if :new.n1 = :new.n2 then
    raise_application_error(-20100,'You can not insert same values');
    end if;
    
    end trig_instab1;
  • 4. Re: Contraint to Chain back a Record
    Solomon Yakobson Guru
    Currently Being Moderated
    Frank Kulash wrote:
    You could do it with a trigger.
    Will not work in multi-session environment. Materialized view would, but it would be delayed (at commit point) reaction.

    SY.
  • 5. Re: Contraint to Chain back a Record
    Solomon Yakobson Guru
    Currently Being Moderated
    Solomon Yakobson wrote:

    Materialized view would
    Actually, it will be problematic to create MV with refresh on commit on a hierarchical query.

    SY.
  • 6. Re: Contraint to Chain back a Record
    Toon.Koppelaars2 Newbie
    Currently Being Moderated
    Does your table only have these two columns?
  • 7. Re: Contraint to Chain back a Record
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Solomon Yakobson wrote:
    Will not work in multi-session environment. Materialized view would, but it would be delayed (at commit point) reaction.
    Good point!
    Actually, there will be a problematic to create MV with refresh on commit on a hierarchical query.
    Here's one way to get a delayed reaction:
    We could schedule a procedure to run periodically (once a day, or once an hour, or whatever) that would check for loops, and send an e-mail, or populate a log table, when it found any. If the table is large, we might want a trigger to populate a DATE column that shows when the last change to a parent or child column took place, and START WITH the rows that were modified since the last time the procedure ran.

    The same procedure could be run by a trigger. If multiple users can change the table at the same time, then a trigger can't prevent loops, but it can report them immediately.
  • 8. Re: Contraint to Chain back a Record
    olivares Newbie
    Currently Being Moderated
    Hi Toon.Koppelaars2,

    The table has other columns.

    I am currently trying some of the other things that other people have mentioned.

    Thanks,

    Olivares
  • 9. Re: Contraint to Chain back a Record
    Toon.Koppelaars2 Newbie
    Currently Being Moderated
    Are any of these columns such that the 'loopback' in your data is not allowed per value of one of these other columns.

    So does this table have one set of rows that are not allowed to loopback into themselves?
    Or is it such that you can have many sets of rows that each are not allowed to loopback into themselves?

    In the former case you cannot have much concurrent transactions on this table.
    In the latter case you can have one transaction per such value on this table.

    Edited by: Toon.Koppelaars2 on 19-feb-2013 7:47
  • 10. Re: Contraint to Chain back a Record
    olivares Newbie
    Currently Being Moderated
    Hi All:

    Thank you for the responses. I am now thinking about create a validation in the application (APEX) to prevent the user from performing this action. Users will be able to chain col2 a col1 record but I am still trying to check it will not loop back to itself.
    For example:
    col1   col2
    1      2
    2      3
    3      1 <- trying to prevent this
    4      2 <- col1 can point to col2 as long as there's no loop back
    Any suggestions?

    Thanks,

    Olivares
  • 11. Re: Contraint to Chain back a Record
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Olivares,
    olivares wrote:
    Hi All:

    Thank you for the responses. I am now thinking about create a validation in the application (APEX) to prevent the user from performing this action.
    If multiple users can be changing the table at the same time, then you'll have the same problem that a trigger has. To get around it, you can:
    (1) Remember what the original parent and child values were,
    (2) COMMIT changes before you know if they create a loop or not,
    (3) Test to see if a loop was created, and, if so
    (4) UPDATE the table back to its original state, with the values saved in step (1).
    Users will be able to chain col2 a col1 record but I am still trying to check it will not loop back to itself.
    For example:
    col1   col2
    1      2
    2      3
    3      1 <- trying to prevent this
    4      2 <- col1 can point to col2 as long as there's no loop back
    Any suggestions?
    You can use CONNECT_BY_ISCYCLE to see if a loop exists.
    You never posted CREATE TABLE and INSERT statements for your table, so I'll use scott.emp to illustrate.
    Say you UPDATE scott.emp:
    UPDATE     emp_table
    SET     mgr     = 7876 
    WHERE     empno     = 7566
    ;
    and you want to know if that created a loop. Run this query:
    SELECT     MAX (CONNECT_BY_ISCYCLE)     AS iscyle
    FROM     emp_table
    START WITH          empno = 7566     -- empno whose mgr you just changed 
    CONNECT BY NOCYCLE     empno = PRIOR mgr
    ;
    The query will always produce exactly 1 row. If the iscycle column on that row is 0, then the DML did not create a loop. If the iscycle column is 1, then a loop exists.

    The query above is a Bottom-Up query, where, given a node, you find all its ancestors. This will be more efficient than the more common Top-Down Query when nodes tend to have fewer ancestors than they have descendants (which is the case in trees).
  • 12. Re: Contraint to Chain back a Record
    rp0428 Guru
    Currently Being Moderated
    >
    Any suggestions?
    >
    Yes - I suggest you respond to Jeneesh.

    What did you find wrong with his solution of using a unique index?
  • 13. Re: Contraint to Chain back a Record
    Solomon Yakobson Guru
    Currently Being Moderated
    rp0428 wrote:
    What did you find wrong with his solution of using a unique index?
    Nothing, except it does not do the job. All it prevents is obvious loops:
    SQL> create table table1 (col1 number,col2 number);
    
    Table created.
    
    SQL>  
    SQL> create unique index u_ndx on table1(least(col1,col2),greatest(col1,col2));
    
    Index created.
    
    SQL>  
    SQL> insert into table1 values(1,3);
    
    1 row created.
    
    SQL> insert into table1 values(3,1);
    insert into table1 values(3,1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.U_NDX) violated
    
    
    SQL> insert into table1 values(3,2)
      2  /
    
    1 row created.
    
    SQL> insert into table1 values(2,1)
      2  /
    
    1 row created.
    
    SQL> select  *
      2    from  table1
      3  /
    
          COL1       COL2
    ---------- ----------
             1          3
             3          2
             2          1
    
    SQL> select  *
      2    from  table1
      3    connect by col1 = prior col2
      4  /
    ERROR:
    ORA-01436: CONNECT BY loop in user data
    
    
    
    no rows selected
    
    SQL> 
    SY.
  • 14. Re: Contraint to Chain back a Record
    rp0428 Guru
    Currently Being Moderated
    Jeneesh also ask this question to try to understand what OP wanted to do
    >
    You mean,you don't want to insert (3,1) and (1,3)
    >
    If OP is only talking about wanting to avoid having two records that directly link to each other the solution appears to work.
1 2 Previous Next

Legend

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