Subject Re: [Firebird-Java] Int field restriction
Author Roman Rokytskyy

first of all, this is more support question that should be asked in
Firebird-Support group.

> I have a smallint field in my database table (Firebird).
> I need make a restriction, at database level, where I can permit only the number 0 (zero) repeat. Numbers greater than 0 can't repeat.

Would the NULL value fit your purposes? If you define the unique
constraint, the rows with NULLs don't count, only the rows with values.
Note that you can create another computed column which would have value
of 0 if the original column is NULL. Also you can create index on
computed column, so searches would be also fast.

However it won't work if you must have NULL and 0s...

> I can do it in my interface application but I want to do it by using database resources.
> I think to create um "check" or other thing. Anyone can help me. What can I do to solve this using database resources?

The check constraint won't help, since in Firebird constraints are
checked when the record is inserted into the database, but the
concurrent trasaction can do it in parallel, but the check constraint
won't see the uncommitted row. Then on commit both records will be