834.txt
     =====================================================================
     No Duplicates Except
     =====================================================================
     PRODUCT:  R:BASE                   VERSION:  5.5 or higher
     =====================================================================
     CATALOG:  General Information      AREA   :  Data Manipulation
     =====================================================================
 
     There are data entry situations where you do not want duplicate
     information entered in a column, but if the data is not known at the
     time of data entry, you want to allow nulls. The standard rule to
     prevent duplicates does not allow null data to be entered. You can
     modify that rule, however, to create a rule that prevents duplicate
     data, but does allow a null value in the column. The following
     technique shows how to modify the default rule to prevent duplicates
     to all NULL values to be entered in the column. You can modify this
     technique to allow entry of a text string or other data value
     instead.
 
     First, create a default rule to prevent duplicate entry in the column.
     In RBDefine, choose the default rule option "Require a unique value."
     If the column you want to check for duplicates was named company,
     R:BASE generates a rule with a WHERE clause that looks like this:
 
     WHERE company IS NOT NULL AND company NOT IN ( SELECT company+
      FROM customer #T1 WHERE #T1.company = customer.company )
 
     The rule has two conditions. The first condition, company IS NOT
     NULL, does not allow NULL values to be entered into the column. The
     second condition, company NOT IN ( SELECT company FROM customer #T1
     WHERE #T1.company = customer.company ), checks for duplicate values.
 
     First, modify the rule and remove the condition that checks for NULL
     values. The rule WHERE clause now looks like this:
 
     WHERE company NOT IN ( SELECT company FROM customer #T1 WHERE
     #T1.company = customer.company )
 
     Next, you need to modify this condition so that it only checks rows
     where company has a value. This part of the rule makes sure that the
     company value you are trying to enter does not already exist in the
     table. The sub-SELECT can't include rows where company is NULL. Add
     the condition, AND #T1.company IS NOT NULL, right before the closing
     parenthesis. The rule WHERE clause now looks like this:
 
     WHERE company NOT IN ( SELECT company FROM customer #T1 WHERE
     #T1.company = customer.company AND #T1.company IS NOT NULL)
 
     Finally, you add a condition, OR company IS NULL, to the WHERE clause
     to allow the column to accept NULL values. The finished rule looks
     like this:
 
     WHERE company NOT IN ( SELECT company FROM customer #T1 WHERE
     #T1.company = customer.company AND #T1.company IS NOT NULL)
     OR
     company IS NULL.
 
     You may have a situation where there are other exceptions you need to
     allow in an otherwise unique column. Instead of checking for and
     allowing NULL values, you can use the same technique to allow a text
     string or other specific value. The key to defining the rule to work
     correctly is to make sure the conditions in the rule WHERE clause
     are mutually exclusive. In this example, our first condition checks
     rows where the company column contains a value, and our second
     condition checks only those rows where the company column is null,
     i.e. it does not contain a value. The same row will never be returned
     by both conditions, they are mutually exclusive.