Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can a FK constraint be ENABLED and VALIDATED and hold false data

291229Oct 3 2007 — edited Apr 18 2008
How can a FK constraint be ENABLED and VALIDATED and clearly hold false data.

Try this example:

--Create the tables and the constraint:
CREATE TABLE temp_parent(parent_pk NUMBER NOT NULL);

ALTER TABLE temp_parent ADD CONSTRAINT temp_parent_PK
PRIMARY KEY (parent_pk);

CREATE TABLE temp_child(child_pk NUMBER NOT NULL, child_fk NUMBER NOT NULL);
ALTER TABLE temp_child ADD CONSTRAINT temp_child_pk
PRIMARY KEY (child_pk);

ALTER TABLE temp_child ADD CONSTRAINT temp_child_fk
FOREIGN KEY (child_fk) REFERENCES temp_parent(parent_pk);

-- Load data:
INSERT INTO temp_parent VALUES(1);
INSERT INTO temp_child SELECT ROWNUM, 1 FROM all_objects;

-- Now touch with the constraints
ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT temp_child_fk;

SELECT status, validated FROM user_constraints u
WHERE table_name= 'TEMP_CHILD'
AND u.constraint_type ='R';

DISABLE VALIDATED <-- Like expected

--Now Delete from the parent:
DELETE FROM temp_parent;

Check status again:
DISABLE VALIDATED <-- Why still VALIDATED?

Now enable the constraint again
ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT temp_child_fk;

Check status again:
ENABLE VALIDATED;

I now have a child table wiht an enabled validated FK. But I don't have any parents in the parent table?

How is this possible? Can you please explain this behaviour? This can't really be a bug or? I guess it's by design but what is the thought behind this?
And does this "feature" mean that we never can trust the data in user_tab_constraints for FK:s or? Isn't the optimizer using this kind of data when it's trying to come up with the best way of joining 2 tables. So if the metadata isn't correct isn't there a risk that the optimizer goes wrong?
And even the query to return the wrong result?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 16 2008
Added on Oct 3 2007
17 comments
4,219 views