FK to sys/system tables. no way.
Shiv, May 24, 2004 - 7:25 am UTC
You are right Tom.
I bet oracle wouldn't support any installation that has referntial intergity with sys or system schema. Other than oracle patches/upgrades I can see likely issues with export/import, transportable tablespaces etc
cheers
Shiv
A reader
A reader, November 15, 2004 - 2:43 am UTC
Is it possible to have Referential integrity between two schemas ?For an example schema A's table refrences schema b's tables and if parent record found then insert otherwise display usual referential integrity error.
Cheers
November 15, 2004 - 6:40 am UTC
yes, yes it is. there is a grant for "references"
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> grant references on t to scott;
Grant succeeded.
A reader, January 27, 2005 - 1:55 pm UTC
I feel (perhaps naively) that it is a good idea to have referential integrity for usernames. For example, our employee table has a username column, whose purpose is to relate employee data to the currently logged in user. When the employee logs into the application, he needs to see which orders have been assigned for him to process today. If an adminstrator makes a typo when entering the username into the employee table, we could run into problems.
I had, in the past, made a materialized view which queries the dba_users view. I gave the MV a primary key, to which the employee table's foreign key pointed. I am now getting the feeling this was not the best idea. Could doing this cause the problems mentioned above (patch problems, export issues, etc.)? Should I abandon my hopes for username referential integrity?
Thank you...
January 27, 2005 - 2:13 pm UTC
it would have been OK, technically. It would not be straight forward, for if you just "did it", you would get:
BEGIN dbms_mview.refresh( 'MV' ); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
on the refresh (it tries to truncate and insert /*+append*/, the truncate fails)
you could use a refresh group to work around that. But they you would get:
BEGIN dbms_refresh.refresh('group1'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02292: integrity constraint (OPS$TKYTE.SYS_C005597) violated - child
record found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
on the refresh (since it deletes all and then inserts all) -- so you would create the constraint on the child table DEFERRABLE.
Then the refresh would work <b>as long as the child table didn't have any refrencing records!!!!</b>
Which brings us straight back to -- no, it wouldn't be practical -- the refresh would "break" every time you dropped a user but still had them in the child table.
I think the list of users that should be in the child table is in fact an application table -- not a dictionary table -- problem.
Value based RI
A reader, November 21, 2006 - 5:17 am UTC
Hi Tom,
We have a generic table for our application, with columns as below:
Atrribute_Name
Attribute_Value
....
Based upon attribute_name we have other tables like
TBL_ADDRESS with address_key.......
TBL_ITEM with item_code.....
What we want to achieve with RI as below:
If in attribute table the Atrribute_Name='ADDRESS' then check with TBL_ADDRESS table that attribute_value (in this case - say 123 as address_key) must exist in TBL_ADDRESS table, similarly, if Atrribute_Name='ITEM' then check with TBL_ITEM table, attribute_value (in this case - item_code ) must exist as in TBL_ITEM table
i.e., we want to achieve value based RI.
Any help will be really useful.
btw: We us Oracle 9.2
Regards
November 22, 2006 - 3:19 pm UTC
hahahaha, this is too good.
please.
use a real data model and you can have RI.
use this and you get bits and bytes. period.
12g....
A reader, November 23, 2006 - 5:28 am UTC
Hi Tom,
Could be a "wish list" for 12g... :-)
Cheers!
Integrity constraint Error even there is no rows to be deleted
Gopal, December 11, 2007 - 9:57 am UTC
Hi Tom,
Please find the following query.
delete
from table1
where col1='TEST'
and col2
and not in ( select c1 from tab1
union all
select c2 from tab2
union all
select c3 from tab3
)
While executing the above query I receive the error :
ORA-02292 integrity constraint (string.string) violated - child record found. As verified with the data manually, the query will delete 0 rows. Even I am getting this integrity constraint error. Please help.
Regards.
Gopal.
December 11, 2007 - 11:05 am UTC
not that I don't believe you ....
but....
do this for us:
select * from table1
where col1='TEST'
and col2
and not in ( select c1 from tab1
union all
select c2 from tab2
union all
select c3 from tab3
)
Integrity constraint error
Gopal, December 12, 2007 - 2:05 am UTC
Thanks for your response.
As you said, I have executed the below query.
select *
from table1
where col1='TEST'
and col2 not in ( select c1 from tab1
union all
select c2 from tab2
union all
select c3 from tab3
)
The above query returns no rows.
Sub query: returns 1
select c1 from tab1 // returns 1
union all
select c2 from tab2 // no rows
union all
select c3 from tab3 // no rows
But while deleting the values, using the below query, it says integrity constraint(ORA-02292) error.
delete table1
where col1='TEST'
and col2 not in ( select c1 from tab1
union all
select c2 from tab2
union all
select c3 from tab3
)
If I substitue the value(1) which is returned be the sub query, it works.
delete table1
where col1='TEST'
and col2 not in (1)
The table1 has only one row
col1 col2
TEST 1
Please help.
Regards,
Gopal.
December 12, 2007 - 8:15 am UTC
give me an example - create tables, inserts to reproduce with.