Check constraint to prevent 2 or more rows from having numeric value of 1

Question

I have a SQL table with a column called [applied], only one row from all rows can be applied ( have the value of 1) all other rows should have the value 0

Is there a check constraint that i can write to force such a case?

Answer 1

If you use null instead of 0, it will be much easier.

Have a CHECK constraint to make sure the (non-null) value = 1. Also have a UNIQUE constraint to only allow a single value 1.

create table testtable (
    id int primary key,
    applied int,
    constraint applied_unique unique (applied),
    constraint applied_eq_1 check (applied = 1)
);

Core ANSI SQL, i.e. expected to work with any database.

Answer 2

To know exactly how to write trigger that will help you an info of a database you use is needed.

You wil need a trigger where this will be your test control:

 SELECT COUNT(APPLIED) 
 FROM TEST
 WHERE APPLIED = 1

If it is > 0 then do not allow insert else allow.

Answer 3

While this can be done with triggers and constraints, they probably require an index. Instead, consider a join table.

create table things_applied (
  id smallint primary key default 1,
  thing_id bigint references things(id) not null,
  check(id = 1)
);

Because the primary key is unique, there can only ever be one row.

The first is activated with an insert.

insert into things_applied (thing_id) values (1);

Change it by updating the row.

update things_applied set thing_id = 2;

To deactivate completely, delete the row.

delete things_applied;

To find the active row, join with the table.

select t.*
from things t
join things_applied ta on ta.thing_id = t.id

To check if it's active at all, count the rows.

select count(id) as active
from things_applied

Try it.

Answer 4

Most databases support filtered indexes:

create unique index unq_t_applied on t(applied) where applied = 1;