What is the easiest way to create a table that can have at most one row? That question was asked on Twitter about two days before I intended to blog about my solution, a month ago.
No triggers necessary
If you can have just one favourite beer:
create table beer (beer varchar2(100), brewery varchar2(42), just_one generated always as (1), constraint just_one_beer unique (just_one));
Add one row:
insert into beer (beer,brewery) values ('My best IPA', 'Dead Brothers Asylum Inc');
Try to add another, and it will fail with ORA-00001:
insert into beer (beer, brewery) values ('Heineken','Heineken Company');
ORA-00001: unique constraint (TESTUSER.JUST_ONE_BEER) violated
Update is not a problem:
update beer set beer='Your best IPA';
And of course you can delete the row:
delete from beer;
By the way, Live SQL by Oracle is perfect for such simple testing. You need a free account at Oracle to do this, but you can create one after you click Start Coding Now.