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 <!--#yiv3810443373 _filtered #yiv3810443373 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv3810443373
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv3810443373
#yiv3810443373 p.yiv3810443373MsoNormal, #yiv3810443373
li.yiv3810443373MsoNormal, #yiv3810443373 div.yiv3810443373MsoNormal
{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv3810443373 a:link, #yiv3810443373
span.yiv3810443373MsoHyperlink
{color:#0563C1;text-decoration:underline;}#yiv3810443373 a:visited,
#yiv3810443373 span.yiv3810443373MsoHyperlinkFollowed
{color:#954F72;text-decoration:underline;}#yiv3810443373
span.yiv3810443373EmailStyle17 {font-family:"Arial",
sans-serif;color:windowtext;font-weight:normal;font-style:normal;}#yiv3810443373
.yiv3810443373MsoChpDefault {font-family:"Calibri", sans-serif;} _filtered
#yiv3810443373 {margin:72.0pt 72.0pt 72.0pt 72.0pt;}#yiv3810443373
div.yiv3810443373WordSection1 {}-->
--
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.
