Philip, I had just asked the same question of an Oracle database I'm currently working with. At first I blamed Oracle but that's just the DB2 in me ;-)
I later found out this is one of the "working as designed" features of a relational database. The following blurb of text comes from an Oracle Concepts book but since it only talks about SQL 92 standards, it should apply to DB2 as well. ****************************************************** NULLS and Foreign Keys The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. Several interpretations of this basic rule of the relational model are possible when composites (multicolumn) foreign keys are involved. The ANSI/ISO SQL 92 (entry-level) standard permits a composite foreign key to contain any value in its non-null columns if any other column is null, even if those non-null values are not found in the referenced key. By using other constraints (for example, NOT NULL and CHECK constraints), you can alter the treatment of partially null foreign keys from this default treatment. A composite foreign key can be all null, all non-null, or partially null. The following terms define three alternative matching rules for composite foreign keys. MATCH FULL Partially null foreign keys are not permitted. Either all components of the foreign key must be null, or the combination of the values contained in the foreign key must appear as the primary or unique key value of a single row of the referenced table. MATCH PARTIAL Partially null composite foreign keys are permitted. Either all composites of the foreign key must be null, or the combination of non-null values contained in the foreign key must appear in the corresponding portion of the primary or unique key value of a single row in the referenced table. MATCH NONE Partially null composite foreign keys are permitted. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key. ****************************************************** Jason Hughes >>> "Philip Nelson (DBA)" <[EMAIL PROTECTED]> 03/26/02 12:28PM >>> DB2 V7.2 on Linux I create a table - T0010STOCK_MARKET_DATA with primary key (EXCHANGE_SYMBOL, SECURITY_SYMBOL); Created another table - T0020TASK with primary key (TASK_ID) and the fields EXCHANGE_SYMBOL and SECURITY_SYMBOL as nullable fields (since every task does not apply to a specific security). Now am able to insert the following records - (TASK_ID, EXCHANGE_SYMBOL, SECURITY_SYMBOL) 1,NULL,NULL (I expect this to work) 1,'US','MSFT' (again I'd expect this) 1, '1', 'MSFT' (this fails : I'd expect that) 1, '1', NULL (didn't expect this) 1, NULL, '1') (or this) So if either of the fields is NULL it allows the insert to proceed. Is this correct ? Phil Nelson ScotDB Limited - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
