Helena Markova, May 16, 2001 - 5:25 am UTC
Recursive relationship
sanjai, January 04, 2002 - 6:43 pm UTC
Tom,
I was wondering what is the best way to resolve many to many recursive relationship.
For example if a employee reports to one or more manager and a manager is being reported by one or more employee.
What is the best way to design the schema for this.
Thanks a lot.
More on recursive relationship
Sue, March 25, 2002 - 1:31 pm UTC
Someone here stumbled across a foreign key in his schema where the parent table and column was the same as the child table and column. What does this mean? I am assuming this was a mistake, but also wondering why Oracle allows this?
ALTER TABLE SCHEMA.TABLE_1
ADD CONSTRAINT FK1_TABLE_1
FOREIGN KEY (COLUMN_A)
REFERENCES SCHEMA.TABLE_1 (COLUMN_A)
/
Thanks,
Sue
March 25, 2002 - 3:15 pm UTC
It looks like a "fast=false" option to me. It'll always allow a row to be inserted, it'll never reject a row, it'll only make things go slower.
I suppose we allow it cause the language doesn't explicity forbid it. Not sure, but it isn't doing anything.
what is your view
A reader, November 21, 2002 - 11:13 am UTC
Hi tom,
In the recursive relationship in the emp table,
SQL> desc emp
Name Null? Type
-------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select empno,mgr,ename from emp;
EMPNO MGR ENAME
---------- ---------- ----------
7369 7902 SMITH
7499 7698 ALLEN
7521 7698 WARD
7566 7839 JONES
7654 7698 MARTIN
7698 7839 BLAKE
7782 7839 CLARK
7788 7566 SCOTT
7839 KING <<--------
7844 7698 TURNER
7876 7788 ADAMS
7900 7698 JAMES
7902 7566 FORD
7934 7782 MILLER
10 kobi
15 rows selected.
SQL> set pages 40
SQL> update emp set mgr = 7839
2 where empno = 7839;
1 row updated.
SQL> commit;
Commit complete.
SQL> select empno,mgr,ename from emp;
EMPNO MGR ENAME
---------- ---------- ----------
7369 7902 SMITH
7499 7698 ALLEN
7521 7698 WARD
7566 7839 JONES
7654 7698 MARTIN
7698 7839 BLAKE
7782 7839 CLARK
7788 7566 SCOTT
7839 7839 KING
7844 7698 TURNER
7876 7788 ADAMS
7900 7698 JAMES
7902 7566 FORD
7934 7782 MILLER
10 kobi
15 rows selected.
SQL> update emp set mgr = null
2 where empno = 7839;
1 row updated.
SQL> commit;
Commit complete.
SQL> update emp set mgr = 10
2 where empno = 7839;
1 row updated.
SQL> rollback
2 ;
Rollback complete.
SQL>
line 3 truncated.
1 INSERT INTO EMP (EMPNO, ENAME, JOB,MGR, HIREDATE,
SAL,COMM, DEPTNO)
2* VALUES (20,'JKIDD','Player',20,sysdate,1000,1000,40)
3 /
1 row created.
SQL> select empno,mgr,ename from emp;
EMPNO MGR ENAME
---------- ---------- ----------
7369 7902 SMITH
7499 7698 ALLEN
7521 7698 WARD
7566 7839 JONES
7654 7698 MARTIN
7698 7839 BLAKE
7782 7839 CLARK
7788 7566 SCOTT
7839 KING
7844 7698 TURNER
7876 7788 ADAMS
7900 7698 JAMES
7902 7566 FORD
7934 7782 MILLER
10 vivek
20 20 JKIDD
16 rows selected.
here
1.) how come we allow 20 empno with 20 mgr
as empno (20) does not exits in the table
2.) also after insert we can update it to the same
parent
-- also what will be the best way to catch all the
integrity violation execptions ( not using OTHERS)
so that for all integrity violation error we can send
a genaric error message to the application ?
Thanks,
November 21, 2002 - 1:20 pm UTC
1) you don't have a foreign key apparently. you don't have RI enabled on this table, there is no foreign key from mgr to empno
2) of course, there is no rule saying that you cannot.
scott@ORA920.US.ORACLE.COM> insert into emp (empno,mgr) values ( 1, 2 );
1 row created.
scott@ORA920.US.ORACLE.COM> rollback;
Rollback complete.
no integrity constraint...
scott@ORA920.US.ORACLE.COM> alter table emp add constraint emp_fk_emp foreign key(mgr) references emp(empno);
Table altered.
scott@ORA920.US.ORACLE.COM> insert into emp (empno,mgr) values ( 1, 2 );
insert into emp (empno,mgr) values ( 1, 2 )
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.EMP_FK_EMP) violated - parent key not found
with integrity constraint...
scott@ORA920.US.ORACLE.COM> rollback;
Rollback complete.
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> declare
2 integrity_constraint exception;
3 pragma exception_init( integrity_constraint, -02291 );
4 begin
5 insert into emp (empno,mgr) values ( 1, 2 );
6 exception
7 when INTEGRITY_CONSTRAINT
8 then
9 raise_application_error( -20001, 'Generic error message number 512' );
10 end;
11 /
declare
*
ERROR at line 1:
ORA-20001: Generic error message number 512
ORA-06512: at line 9
and that is how to map and oracle error code to a user defined exception that you can catch by name
Thanks
A reader, November 21, 2002 - 1:55 pm UTC
Thanks tom .
Unindexed FK for this case
MIke, November 04, 2003 - 11:11 pm UTC
Is there issue for without indexing for the FK on Mgr?
November 05, 2003 - 8:13 am UTC
only if you
a) update DEPT's primary key (bad bad hugely bad idea)
b) delete from DEPT
if you do either of those, yes, you want to index deptno in EMP
for purposes of this example, no, there would be no difference.
A reader, November 05, 2003 - 10:26 am UTC
"
only if you
a) update DEPT's primary key (bad bad hugely bad idea)
b) delete from DEPT
if you do either of those, yes, you want to index deptno in EMP
for purposes of this example, no, there would be no difference.
"
what about insert?
November 05, 2003 - 6:07 pm UTC
insert, fine.
avoiding recursive hierarchy
OracleO, July 31, 2005 - 4:38 am UTC
Hi Tom,
I have a situtaion where in i get errror ORA-01436 "connect by loop in user data". Can i implement any trigger or constraint that can keep data integrated and avoid this error...
I create a table with the following script
create table test
(id number
id1 number);
ALTER TABLE TEST ADD (
CONSTRAINT PK PRIMARY KEY (ID));
ALTER TABLE TEST ADD (
CONSTRAINT FK FOREIGN KEY (ID1)
REFERENCES TEST (ID));
INSERT INTO TEST ( ID, ID1 ) VALUES ( 1, NULL);
INSERT INTO TEST ( ID, ID1 ) VALUES ( 2, 1);
INSERT INTO TEST ( ID, ID1 ) VALUES ( 3, 2);
INSERT INTO TEST ( ID, ID1 ) VALUES ( 4, 2);
INSERT INTO TEST ( ID, ID1 ) VALUES ( 5, 4);
INSERT INTO TEST ( ID, ID1 ) VALUES ( 6, 5);
COMMIT;
select id,id1,level from test
connect by prior id=id1
start with id1 is null;
output is as follows
ID ID1 LEVEL
-----------------------
1 1
2 1 2
3 2 3
4 2 3
5 4 4
6 5 5
now i update id1 of id 2 to 5.
update id1=5 where id =2;
select id,id1,level from test
connect by prior id=id1
start with id1 =2 --or 5
/
i get ora-01436..
The requirement which i need to provide is that user should not be able to update records that can create this kind of error..
Thanks,
July 31, 2005 - 9:11 am UTC
You'll want to perform modifications to this table in a stored procedure, and you'll need to serialize access to the table.
In that fashion, you can insert/update the record and walk the tree starting with this modified record to ensure it doesn't raise the 1436.
but you'll want to lock the table so as to ensure two people don't attempt this simultaneously - else you'd be right back where you started.
You can in theory do this in a trigger, but I'm not a big fan of mysterious things happening by side effect in a trigger running in the background.
How can I write this better...
A reader, August 15, 2007 - 9:00 pm UTC
Tom,
There are times where you need to write something like this to get the description of a column from the same
table just changing a few fields..
......WHERE inar.inal_alert_id = inal.inal_alert_id
AND inar.cdt_request_status_cd = 'STATUS_ACT'
AND ( table1.cdt_code_cd = inev.cdt_event_category_cd
AND table1.cdt_language_cd = 'LANG_EN'
AND inev.event_id = inal.inev_event_id
AND inev.status_cd = 'STATUS_ACTIVE'
)
AND ( inot2.cdt_code_cd = inev.cdt_event_item_cd
AND inot2.cdt_language_cd = 'LANG_EN'
AND inev.event_id = inal.inev_event_id
AND inev.event_status_cd = 'STATUS_ACTIVE'
)
AND ( inat1.alert_id = inal.inal_alert_id
AND inat1.cdt_alert_attribute_cd = 'ALERT_IMPORTANCE'
AND inat1.cdt_attribute_status_cd = 'STATUS_ACT' .......
August 20, 2007 - 7:23 pm UTC
I don't have any idea what I'm even looking at here.
follow-up
A reader, August 21, 2007 - 10:41 am UTC
It was just a piece of code I took from my sql statement. Basically I was looking for you input as to how to handle
situation where you need to refer to the same table to get
a description.
August 22, 2007 - 11:47 am UTC
my original statement stands. I have no clue what you are attempting to do based on that snippet.
Suggestion regarding implementing recursive calls
Mandm, August 16, 2010 - 3:19 pm UTC
I came across a rather interesting requirement today and wanted to know some optimal ways about resolving this
I have a table A with A1, A2, A3, A4
Data is
A1 A2 A3 A4
1 100 10
1 101 11
1 102 12
1 103 13
2 104 10
2 105 11
2 106 12
3 107 10
3 109 11
3 110 15
4 111 15
4 112 16
4 113 17
5 114 16
5 115 17
I have to identify this as one group and specify a unique number in the column A4
When i was going through the logic, i came up with some recursive logic
e.g
First Call
A1 A3
1 10 11 12 13
Second call
A1 A3
1 2 3 10 11 12 13 15
Third call
A1 A3
1 2 3 4 10 11 12 13 15 16 17
Forth call
A1 A3
1 2 3 4 5 10 11 12 13 15 16 17
and thats when i have to stop when A3 in the consecutive calls are same and then update column A4 with a unique ID
I am pretty sure this is not possible in a SQL statement, unless you tell me otherwise, else should i do recursive function or recursive procedures
The table size is around 50K rows
Thanks a lot !
A reader, December 04, 2015 - 11:44 am UTC
15 years later, your answer still has gold value !