Table with one row

April 5, 2022

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.

My solution is practically the same as what @FranckPachot suggested, but that is not stopping me from blogging mine.

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.