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

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

1. make it be in the parent tables
2. 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