How can a FK constraint be ENABLED and VALIDATED and hold false data
291229Oct 3 2007 — edited Apr 18 2008How 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?