Re: Help for Oracle documentation/turning off foreign key constraints

Thank you very much for spending time answering this clearly.

> while we can do this (you can enable novalidate the constraint), I see
> nothing
> but heartburn coming from such a concept.
>
> the entire premise of the foreign key is to ensure users of the data that
> "yes,
> in fact, if you join -- it'll be there, this data is clean"

When you put it like this it makes sence. I will populate the missing information with values/fix the data.

Thanks you & Merry Christmas
Monty

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:113899049.000183e8.082_at_drn.newsguy.com...
> In article <6284dd3.0412240223.71e7fb13_at_posting.google.com>, Monty says...
>>
>>Hi newsgroup, I have not read the Oracle documentation. I need to now
>>what to search on to find what I am looking for in the Oracle
>>documentation. This is where I need help, what search terms do I use
>>to search the Oracle documentation?
>>
>>Here is my problem. I have a tables with not null foreign key
>>constraints to other tables. All new data populated into the tables
>>satisfies the foreign key constraints. Some historical data does not.
>>Some databases offer the facility to 'turn-off' foreign key
>>constraints sometimes. I want to load the old data into the tables
>>with the foreign key constraints off. I then want to turn them on for
>>all new data. I need to know what 'term' or what this functionality is
>>called to search the Oracle documentation for to find out how or if I
>>can do this in Oracle. I am using Oracle 9i on Mandrake Pentium 4.
>>
>>Thank you
>>
>>Monty
>
> while we can do this (you can enable novalidate the constraint), I see
> nothing
> but heartburn coming from such a concept.
>
> the entire premise of the foreign key is to ensure users of the data that
> "yes,
> in fact, if you join -- it'll be there, this data is clean"
>
> the use of the enable novalidate is for a DW whereby you've scrubbed the
> data
> already and would like to put the constraint on without checking again
> (you
> already checked).
>
> Data is important (or you wouldn't be putting a fkey on this in the first
> place!). I suggest you fix the data, if the historical values are in fact
> not
> in the parent tables you either
>
> a) make it be in the parent tables
> b) allow nulls in the fkey and put null in there and move the historical
> data to
> some other field for reference only.
>
>
>
> ops$tkyte_at_ORA9IR2> create table p ( x int primary key, y int );
>
> Table created.
>
> ops$tkyte_at_ORA9IR2> create table c ( a int not null, b int );
>
> Table created.
>
> ops$tkyte_at_ORA9IR2>
> ops$tkyte_at_ORA9IR2>
> ops$tkyte_at_ORA9IR2> insert into c values ( -1, 1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA9IR2> insert into c values ( -2, 1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA9IR2> alter table c add constraint really_bad_idea foreign
> key(a)
> references p enable novalidate;
>
> Table altered.
>
> ops$tkyte_at_ORA9IR2>
> ops$tkyte_at_ORA9IR2> insert into c select * from c;
> insert into c select * from c
> *
> ERROR at line 1:
> ORA-02291: integrity constraint (OPS$TKYTE.REALLY_BAD_IDEA) violated -
> parent
> key not found
>
>
> --
> Thomas Kyte
> Oracle Public Sector
> http://asktom.oracle.com/
> opinions are my own and may not reflect those of Oracle Corporation
Received on Fri Dec 24 2004 - 09:39:55 CST