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.
Anybody have a suggestion for the best way to ensure that an #oracledatabase table that already has one row can not have another row inserted into it?#sql #plsql
— SF on PL/SQL (@sfonplsql) March 2, 2022
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.