In a table one column has typically unique values, but there are exception, so we can not use a UNIQUE constraint.
What good practices/patterns exist for such cases?
A simple solution is to do a SELECT first to see if the value is already in the table and then either insert or show a warning (and insert anyway if the user decides so). This of course misses the case when the same value is inserted by two users (sessions) at the “same” time.
One idea to handle that I found was to use a helper table to do a lock for the value being inserted, something like:
create table main (
itemId INTEGER PRIMARY KEY,
foo VARCHAR2(100),
sometimes_unique VARCHAR2(100) -- this is the "mostly unique" column
);
create table main_lock_helper
(
sometimes_unique VARCHAR2(100) PRIMARY KEY -- UNIQUE would work too?
);
----
-- the insertion code
INSERT INTO main_lock_helper(sometimes_unique)
VALUES (:new_value); -- this will block all other users trying to insert the same value
SELECT count(*) from main where sometimes_unique = :new_value;
IF count>0 THEN WARN ELSE INSERT...
Alternatives:
-
use DBMS_LOCK instead of the “lock table”, using a lock name like ‘locking.for.table.main.sometimes_unique.’||:new_value
-
lock the entire table “main”
Any better ideas?