Foreign key to a composite unique key with nulls
Searching for opinions here...
Picture this..
Foreign key to a unique composite key with columns that allow null values?
The reason why I ask is that when you do a equi-join on the pk and fk columns in a select statement the records having a null value in the key do not appear in the result becase "null does not equal null". Is this uk -> fk relationship OK and the developer just needs to remember to do some screwy where clause when joining like...
where nvl(a.col1, '*') = nvl(b.col_1, '*') and
nvl(a.col2, '*') = nvl(b.col2, '*');
I've never seen this done in 14 years. Have you? Im trying to establish some FK constraints on a database designed before FK constraints existed, pre Oracle 7, without rewriting or redesigning the database.
By the way.. Ive tested this on Oracle 9.2 and this relationship is permitted and the nvl query appears to work fine.
Thanks for comments.
Message was edited by:
Mark Reichman