> In
> `events`, my judge_id is signed, and a negative value like -1 means
> 'not
> applicable' (to the application) and so forth. At event insertion/
> update
> time I will go to great pains to enforce RI, e.g., by locking tables
> and
> making sure nobody has deleted judge so-and-so out from under me.
>
> What solution would you suggest?
Right -- you can't use -1 if you want DRI, because there is no judge
whose primary key value is -1.
You should make the judge_id column nullable, and use NULL to mean
'not applicable' or 'not assigned'. Then you can declare a foreign
key constraint to the judges table. A NULL in the foreign key column
doesn't have to match any row in the referenced table.
You can read more about using NULL in my book "SQL Antipatterns" (http://www.pragprog.com/titles/bksqla/
), in the chapter "Fear of the Unknown."
That said, I think it's weird to delete a row from the judges table
regardless. Does that mean when a judge retires, you delete his row,
and the delete cascades to any events that he or she participated in?
Doesn't that mean destroying a lot of useful historical data? Not to
mention erasing the legacy of a respectable career in public service?
I would think that in an app like this, you would never actually
DELETE a row from judges. Instead, you set a `status` attribute for
that judge, to mark him or her as 'retired' or 'voted out of office'
or something. Or else set a date field for `end_active_term`. Then
code your application logic to create new events only for active
judges, e.g. those whose `status` is 'active', or whose
`end_active_term` is either NULL or in the future.
If you don't delete, then you don't need to cascade deletes. :-)
Regards,
Bill Karwin
没有评论:
发表评论