Help ?? - USE OF DEFAULTS IN AN RDBMS IMPLEMENTATION

USE OF DEFAULTS IN AN RDBMS IMPLEMENTATION The debate on nulls in SQL has all but exhausted RDBMS users and the relational DBMS authorities have still left us all with a conundrum.

Opinions include:

1. SQL standards bodies who voted for 3-valued logic and seem to think nulls are no worse than defaults - see Jim Melton and Alan R Simon "Understanding the new SQL: a complete guide" (Morgan Kaufmann, 1993) p35.
2. E. Codd prefers two types of nulls (for 'unknown' and 'not applicable') and 4-valued logic.
3. C. J. Date wants a 2-valued logic, default values approach that involves dropping the entity integrity rule (ie. a primary key can have a default value, say -1 meaning 'unknown', hence tables contain 'dummy' rows) and the referential integrity rule can be redefined so that there is no need for exceptions for null foreign keys - see C.J. Date "The Default Values Approach to Missing Information" in Relational Database Writings 1989-1991 (Addison-Wesley 1992).
4. David McGoveran also thinks we should use 2-valued logic but he doesn't think dummy rows should be created (ie. keep the entity integrity rule). Instead an extended DBMS system catalogue should be used to distinguish equivalent values entered by users from DBMS assigned defaults - see David McGoveran "Nothing from Nothing The Conclusion" Database programming and Design, Vol.7 No.3 Mar 94.

While many have experienced the delights of SQL nulls has anyone developed a non-trivial information system based on the default values approach? How did it go? What are the consequences and what are the difficulties? Was C.J.Date's approach adopted or that of David McGoveran?

There is also another default values approach which none of the authorities has mentioned, viz. default values in foreign keys do not reference any rows. This model is therefore based on:

(a) Entity integrity: No component of the primary key of a base

      relation is allowed to accept nulls (ie. missing information such
      'not applicable' or 'unknown').



  (b) Referential integrity rule: The database must not contain any


      unmatched foreign keys unless the foreign keys represent
      missing information.

Implementation of this proposal with todays RDBMS products requires referential integrity (RI) to be specified in triggers and stored procedures. Hence this proposal cannot be implemented on a DBMS which has SQL 92 conforming or declarative RI only.

Has anyone implemented a system based on the default values approach using the above technique? Should this approach be used at all?

The default values approach implemented at the user level with todays products clearly has problems not the least of which is the fact that the results of a query may contain SQL nulls anyway hence queries on these tables involve 3-valued logic.

So do we all just give up and develop systems based on SQL nulls and 3-valued logic (and write pleading letters to vendors and standards committees to lead us out of this mess) or do we attempt to use default values with current RDBMS? Received on Mon Aug 29 1994 - 03:24:24 CEST