When we specify a foregin key relationship in oracle, we do not need to say whether relationship is 1 to 1, 1 to many etc. But when we specify a relationship in the cognos framework manager, why we need to specify 1 to 1, 1 to many etc? Also, unrelated but just curious, the same is the behavior in a ORM tool like Hibernate. Is the specification only for optmisation purposes?
My other question is what is th effect of specifying a wrong cardinality relationship? i,e, I specify a 1 to 1 relationship where it is actually 1 to many. What is the effect i am risking?
You do declare the relationships in Oracle, the nature of the relationship is implicit in the way that it is declared to Oracle, e.g.:
CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent_table (id)
implies that there is a 1:M relationship between the parent table and this table.
If, in addition, there was a unique constraint on the child table, e.g.:
CONSTRAINT uk UNIQUE (id)
implies that the relationship is 1:1.