Re: Custom Constraint

On Thu, May 10, 2001 at 11:18:56AM -0700, David Wheeler wrote:
> Hi All,
>
> I need to create a custom constraint (or a trigger?) on a table, and could
> use some help. What I have is a table with a user_name (varchar) column
> and an active (int 1) column. What I want to do is to enforce unique user
> names, but only for an active user. That is, there can be n rows with the
> same user name as long as the value in the active column is 0. There can
> be only one row with that same user name, however, where the active value
> is 1.

Might it be easier to have two tables? one for active users and one for
inactive users?  Don't know how you determine activity status, but if
you just want to "archive" formerly active users, it may be better to
keep them in separate tables and use an ON DELETE rule for the active
users table which does an insert into the inactive users table.  If the
user is to be reactivated, you'll still have the problem that the old
user name may conflict with a new user name.

What you're asking to do violates concepts of normalization.  I suspect
performance would be better with the two table approach as well.  I'm
guessing you must have additional information identifying users,
otherwise I don't see the point of having duplicate entries in the
inactive users table (unless you want to do statistics on name usage ;)

--
Eric G. Miller <egm2@jps.net>