1 2 Previous Next 18 Replies Latest reply: Feb 20, 2013 12:11 AM by Billy~Verreynne RSS

    Contraint to Chain back a Record

    olivares
      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
          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
            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
              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
                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
                  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_Koppelaars-Oracle
                    Does your table only have these two columns?
                    • 7. Re: Contraint to Chain back a Record
                      Frank Kulash
                      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
                        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_Koppelaars-Oracle
                          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
                            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
                              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
                                >
                                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
                                  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
                                    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