There might be another way without creating another table.
Create an autonum column on said table, call this column “pk”. Create a computed column with a unique index with the following calculation: Ifnull(column to be tested, pk, column to be tested) If column to be tested is a date or other non-numeric data type: Ifnull(ctxt(column to be tested), ctxt(pk), ctxt(column to be tested)) From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Markson Sent: Friday, May 06, 2016 11:48 AM To: [email protected] Subject: RE: [RBASE-L] - Unique or NULL Thanks, Larry. Very helpful. I avoid R:Base RULEs completely, but, you're right, choice #2 is correct! Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: 'Lawrence Lustig' via RBASE-L [mailto:[email protected]] Sent: May-06-16 10:51 AM To: [email protected] Subject: Re: [RBASE-L] - Unique or NULL What is the best way to implement a column that can be null, but if not null, must be unique? This cannot be accomplished through the standard SQL constraint mechanism. UNIQUE columns cannot be NULL. You have two choices: 1. Use a RULE. This is R:Base specific and not foolproof since RULEs can be turned ON or OFF. This may be an advantage or a disadvantage in your situation. This is probably the easiest solution (especially if the column already exists) as long as you're okay with running with RULES ON most of the time. 2. Move the unique-if-not-NULL column to a separate table, linked back to the main table by a PK/FK relationship. Create a UNIQUE index on the column in the new table. This uses only standard SQL features and, if properly setup, cannot be worked-around. If there is additional data associated with the unique-if-not-NULL identifier (dates, scores, etc) then this is definitely the "correct" solution. -- Larry -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
