[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Rely foreign key constraint in 9i/10g

Posted on 2006-05-22
15
Medium Priority
?
9,308 Views
Last Modified: 2011-08-18
Hello,

On 9i it goes as supposed:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>  create table pt (id number, value number);

Table created.

SQL>  create table ft (id number, value number);

Table created.

SQL>  alter table pt add constraint pt_pk primary key(id);

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;

Table altered.

But on 10g I'm encountering problems:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table pt (id number, value number);

Table created.

SQL> create table ft (id number, value number);

Table created.

SQL> alter table pt add constraint pt_pk primary key(id);

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;
alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate
*
ERROR at line 1:
ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

I've noticed that when I
SQL> create table pt (id number, value number);

Table created.

SQL> create table ft (id number, value number);

Table created.

SQL> alter table pt add constraint pt_pk primary key(id) rely;

Table altered.

SQL> alter table ft add constraint fk_ft foreign key(value) references pt(id) rely disable novalidate;

Table altered.

on my 10g it's ok - but what will be impact of having rely PK? And why does it differ on 9i/10g?

Thanks,
Grzegorz
0
Comment
Question by:GGuzdziol
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16732499
Hello GGuzdziol,

Here is what the error means
ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY
Cause: RELY is specified for the foreign key contraint, when the associated primary key constraint is NORELY.
Action: Change the option of the primary key also to RELY.

HTH

I
0
 
LVL 14

Author Comment

by:GGuzdziol
ID: 16732598
ivostoykov,

Let's assume that I've already done some google'ing for that error :-) And I've even put an example how do I do that with rely PK. The question is in my last sentence.

Thanks,
Grzegorz
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16732776
Hi GGuzdziol,

Specify RELY to enable an existing constraint without enforcement. Specify NORELY to enable and enforce an existing constraint. The default is NORELY.



RELY you can use it in a data warehouse where you have loaded "scrubbed" data.  You can have a constraint enabled without being enforced (trust me -- that column  is UNIQUE, that foreign key -- its OK, that check constraint -- its accurate).  We can use that in query rewrites against materialized views.  If you are interested in a larger example showing the query rewrite (takes a bit of setup and walking through) I have a chapter on materialized views in my book that describes the use of this setting
and why its relevant.

Constraint States:

                 RELY NORELY
ENFORCED           No No
TRUSTED           Yes No
STALE_TOLERATED   Yes No


it has to do with query rewrite (materialized views, views) and when the constraint will be "used"

In query_rewrite_integrity = TRUSTED -- if the constraint was put in with "norely" -- it would not be used for rewrite (we would not assume the fkey was
really 'there' and in place).  for rely -- it would use it (even if Oracle has not validated the "fact")


regards

I
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 14

Author Comment

by:GGuzdziol
ID: 16732927
So You say that setting PK to rely should have no impact assuming historical data is enforced to be accurate (unique)?

I mean: it will keep new data clean (unique) and will have no performance impact, is that right?

And one more question - is there any scenario that it's reasonable to put RELY option without corresponding NOVALIDATE?

Thx
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16733095
Hi GGuzdziol,

well this meand that it will impact query rewriting. It stated whether the server should RELY on a constraint when re-writing queries using materialized views and
such (should it *trust* you).

it does not change anything - just norely will cause re-validating.

As for the last question - for instance NULL/NOT NULL are never 'relied' upon unless validated.

regards

I
0
 
LVL 14

Author Comment

by:GGuzdziol
ID: 16733216
But what would happen if I created PK just as
alter table ... add constraint .. primary key(...)
- would optimizer rely on it? I'd just like to know if I can break something (i.e. performance) with creating most of my PK's with RELY option.

Full scenario is;
I'm now on 9i and as stated in my first post problem does not exist - I can create FK's with novalidate/disable/rely options and without touching PK. We're planning to migrate to 10g and it turned out something has changed. First natural workaround is to create pk's with rely option - and I don't know if it's ok.
Or maybe there's different solution then create pk with rely option?

Thanks
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16733310
Hi GGuzdziol,

> would optimizer rely on i
well this is a hard question. I cannot answer it because i do not know the structure of the data.

What I could suject by heart is to try with RELY option on. But *only* with/without RELY option won't affect performance.

Finally I would suggest: test, test, test...

regards

I
0
 
LVL 3

Expert Comment

by:JacekMycha
ID: 16733977
1. As already noted, RELY/NORELY clause is used solely by query rewrite.
DISABLE NOVALIDATE RELY means: "I don't want an index and constaraint checking to slow down my batch data loading into datawarehouse, but the optimizer can RELY on my data loading routine and assume this constraint is enforced by other mechanism". This information can greatly help optimizer to use correct materialized view when rewriting queries. So if you don't use materialized views for query rewrite then you can put RELY for all your constraints (or NORELY for all your constraits) and forget about it.

2. RELY/NORELY only matters in  NOVALIDATE state. If a constraint is in VALIDATE state then optimizer is sure that constraint is enforced and it doesn't have to rely on your declaration.

3. By default, a constraint is created as NORELY. So your primary key is ENABLE VALIDATE NORELY (for historical reasons).

4. Oracle 9i did not have ORA-25158 error. A 'creative' developer can create a PRIMARY KEY in DISABLE NOVALIDATE NORELY state (instructing the optimzer "don't trust me"), enter duplicate primary keys and then create a foreign key in DISABLE NOVALIDATE RELY  state (at the same time telling optimizer "trust me"). This makes the optimizer confused and unhappy.

5. Conclusion: Because your primary key is by default ENABLE VALIDATE, you can create it as RELY. It doesn't matter as long as your primary key is VALIDATEd.

JacekMycha
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16735147
why do you need to use "rely" constraint? Are you in data warehouse? Do you use ETL process to get all the data?

as the above, "RELY" is a kind "trust me" mode. It is ususally used on materialized views or large data in data warehouse. Before you use it,  you need to know all implication along with it.

you can refer to Oracle data warehouse document for furthur information.


acton
0
 
LVL 14

Author Comment

by:GGuzdziol
ID: 16735272
>>  Are you in data warehouse?
Yes

>> Do you use ETL process to get all the data?
Yes

My main concern is: will changing PK from ,,norely enable validate'' to ,,rely enable validate'' have any implication?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16735358
>>"rely enable validate"

as JackeMycha said, rely only tell optimizer "trust me" whehter or not primary key is enabled or disabled. It sounds like "TRUST ME" even if the constraint is disabled.

Of couse, "TRUST ME" as constraint is enabled  looks like a waste but no harm to me.

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16735371
Just curious: why you put a rely disabled fk but a norely enabled pk before in 9i? looks not consistent to me.
0
 
LVL 14

Author Comment

by:GGuzdziol
ID: 16735765
As far as I know norely gives nothing when it's combined with enable and validate. PK is enabled to keep primary key valid (and since unique index is used it doesn't cost too much - does it?)

FK probably is rely novalidate disable for performance reasons (I'm not author of this system that's why I'm only guessing) - do not check but enable query rewrites.

From logical point of view RELY/NORELY option for constraint that is enabled and validated makes no sense for me - I'd suppose that oracle should ignore it. But I do not know that - that's why I'm asking. I am pretty sure that someone must have encountered such a problem before (as rely disable novalidate foreign key is standard mechanism fo olap and it fails on 10g with PK that's validated and enabled - and with default norely option)
0
 
LVL 3

Accepted Solution

by:
JacekMycha earned 2000 total points
ID: 16741107
RELY/NORELY gives nothing when it is combined with VALIDATE. (ENABLE/DISABLE is not important).
Changing your PK from ,,norely enable validate'' to ,,rely enable validate'' will have no implication, because your constraint is still VALIDATE.

Each constraint property can be altered independently. You declare your primary key RELY/NORELY in case it ever goes NOVALIDATE. You can issue ALTER TABLE pt MODIFY CONSTRAINT pt_pk NOVALIDATE at any time. Oracle 10g simply prevents NOVALIDATE RELY foreign key that references NOVALIDATE NORELY primary key. We can say that there is 'a bug' in 9i, because it allows inconsistency between primary key state and foreign key state. It was corrected in 10g.

If the ETL procedures ensure uniqueness of primary key colum (e.g. they are generated from sequence) then a developer can DISABLE NOVALIDATE (drop primary key index) during batch load (faster load without index updates) and ENABLE VALIDATE it (build index) later. Or he/she may decide not to build the index on primary key if it is not used by typical queries. There are many possible scenarios in datawarehouse.

JacekMycha

0
 
LVL 14

Author Comment

by:GGuzdziol
ID: 16790445
Thanks for Your help!

I suppose Jacek's explanation was the best, so points go to him.

And just FYI: http://tkyte.blogspot.com/2006/01/stuck-in-rut.html

Cheers!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question