> We use a "is_deleted" to show wheter a row is active or deleted. We have
> a rule that says that no two rows in our table can contain the same
> value for column "name" if the value for column "is_deleted" is set to
> false.
>
> For example, this is valid data:
>
> ID | Name | IS_DELETED
> ---------------------------------
> ID1| Name 1 | 'F'
> ID2| Name 1 | 'T'
> ID3| Name 1 | 'T'
>
> While this is not:
>
> ID | Name | IS_DELETED
> ---------------------------------
> ID1| Name 1 | 'F'
> ID2| Name 1 | 'F'
> ID3| Name 1 | 'T'
>
>
> Is there a clean way for me to enforce this at the database level or
> should I take care of it at the application level?
>
> Thanks,
>
> L

I hope your NAME field is a relatively short VARCHAR - say 100 or less, and
it's indexed.
Then I would use ON INSERT and ON UPDATE triggers to check the presence of
the false is_deleted condition against the name.
I would have to raise one exception type on insert if the name exists and
another if it exists on update. Maybe your app could then rollback the
insert and let the operator choose to delete the already existing duplicate.
( I assume you can undelete records..)
I think, though, that this kind of check will not stand up too well under
heavy multiuser use since your trigger checks will only see what's visible
to it... if someone has not committed a change which would invalidate your
data, your insert will not fail and neither will theirs.
In which case you may need some "housekeeping" routines.
Alan