Wednesday, October 03, 2007

Interesting relational integrity bug

Over in the OTN forums, Wilhelm demonstrates a method for disabling foreign key constraints in such a way that we can delete the parent data and then re-enable the foreign key without throwing an ORA-02298 exception.

The proper syntax for disabling a constraint is this:

SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL> delete from temp_parent;

2 rows deleted.

SQL> ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk;
ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT what_the_fk
*
ERROR at line 1:
ORA-02298: cannot validate (APC.WHAT_THE_FK) - parent keys not found


SQL>

But if we include the mystical keyword VALIDATE in the disabling command we can corrupt our relational integrity quite nicely:

SQL> INSERT INTO temp_parent VALUES(1);

1 row created.

SQL> INSERT INTO temp_parent VALUES(2);

1 row created.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> delete from temp_parent;

2 rows deleted.

SQL> ALTER TABLE temp_child ENABLE CONSTRAINT what_the_fk;

Table altered.

SQL> select * from temp_parent;

no rows selected

SQL>

Labels: , ,

5 Comments:

Anonymous Anonymous said...

Excellent naming convention!

3 October 2007 at 15:38:00 GMT-7  
Blogger Noons said...

akshally, to be able to enter non-validated fks in an enabled constraint is indeed a perfectly legit facility.

it's well used in large datamart and dw loads.

in fact, IIRC it is possible to enable the validation back and let it dump into another table any fks that fail validation.

perfectly kosher and indeed weolcome in complex data loads into complex schemas.

3 October 2007 at 23:03:00 GMT-7  
Blogger APC said...

>> akshally, to be able to enter
>> non-validated fks in an enabled
>> constraint is indeed a perfectly
>> legit facility.

I wouldn't argue with that. This behaviour is the exact oppposite: disabled yet validated constraints. If you read my follow-up post you'll see that it is actually impossible to insert into a table which has a constraint in that position.

Cheers, APC

3 October 2007 at 23:41:00 GMT-7  
Anonymous Anonymous said...

SEO, search engine optimization. Is to make your Web site or Blog search engines more popular in other search-related content, as far as possible to make your site appear in the results of the first of several. This will bring a wow power leveling lot of traffic, instead of complaining all day: Why am I the one does not see. At present, some of my traffic from search engine Baidu and is the most stable source of traffic. I almost did not do anything, so naturally things happened. If the SEO from the point of view, I probably spent the most stupid and most simple way, but really effective. If the search engine as a beauty, then what is the point of a simple way to let her eyes you see more of it? First, you havewow powerleveling enough fresh interesting. Second, you are unique and eye-catching. Third, you are indeed very interesting new connotation enough to say that the speed of your time in the first issued a message, followed by everyone from here to you, then you are a source of information. Found on the girl has great respect for the source of information, as your grasp, it would wow gold be tantamount to grasp the numerous reproduced the contents of your site. In that case, she must be approved by the Changlaikankan your station. The fastest you that she is from here you get the fastest, so her customers can also receive up-to-date search results. So, note to Mars will not do. The result is you confuse the site in a large site on Mars, found the girl simply can not tell to your face.

28 November 2008 at 06:56:00 GMT-8  
Blogger chenlu said...

runescape money
runescape gold
runescape money
runescape gold
buy runescape gold buy runescape money runescape items
runescape accounts
runescape gp
runescape money
runescape power leveling
runescape money
runescape gold
dofus kamas
cheap runescape money
cheap runescape gold
Guild Wars Gold
buy Guild Wars Gold
lotro gold
buy lotro gold
lotro gold
buy lotro gold
lotro gold
buy lotro gold

Hellgate Palladium
Hellgate London Palladium
Hellgate money
Tabula Rasa gold tabula rasa money
Tabula Rasa Credit
Tabula Rasa Credits
Hellgate gold
Hellgate London gold
wow power leveling
wow powerleveling
Warcraft PowerLeveling
Warcraft Power Leveling
World of Warcraft PowerLeveling World of Warcraft Power Leveling runescape power leveling
runescape powerleveling
eve isk
eve online isk
eve isk
eve online isk
tibia gold
Fiesta Silver
Fiesta Gold
Age of Conan Gold
buy Age of Conan Gold
aoc gold

呼吸机
无创呼吸机
家用呼吸机
呼吸机
家用呼吸机
美国呼吸机
篮球培训
篮球培训班
篮球夏令营
china tour
beijing tour
beijing travel
china tour
tibet tour
tibet travel
computer monitoring software
employee monitoring

16 February 2009 at 22:22:00 GMT-8  

Post a Comment

<< Home