Postgresql – Restrict insertion of new row if a row exists with certain field values

constraintpostgresql

Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table.

e. g row 1

withdrawal, txn1

I am trying to add a constraint where it restricts to insert new row with txn_type <> 'withdrawal' and txn_id = txn1.

e.g it restricts to insert row with following values

non_withdrawal, txn1

My situation is like if a record exist with transaction_type = withdrawal & txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted.

Best Answer

Answer left in a comment by matigo:

Two ways to do this would be through a BEFORE INSERT trigger or via a stored procedure. With a trigger, you can be sure that every application that writes directly to the database must follow the prescribed rules. With a stored procedure you can (potentially) simplify the entire process if there are multiple steps that must be followed in succession.

Related Solutions

Sql-server – Insert New Foreign Key Row for new Strings

Assuming someval is actually an auto-generated column like a serial.

In Postgres this data-modifying CTE (writeable CTE) would do the job:

WITH sel AS (
   SELECT r.someval, i.someval_str, i.machine
   FROM  (SELECT ?, ?) AS i(someval_str, machine)
   LEFT   JOIN new_data_someval_ref r USING (someval_str)
   )
, ins AS ( 
   INSERT INTO new_data_someval_ref (someval_str)
   SELECT DISTINCT someval_str FROM sel WHERE someval IS NULL
   RETURNING someval, someval_str
   )
INSERT INTO new_data (someval, machine)
SELECT COALESCE(sel.someval, ins.someval), sel.machine
FROM   sel
LEFT   JOIN ins USING (someval_str);

Everything standard SQL, not sure whether it's all implemented in SQL Server identically.

For a query with multiple input rows and ample explanation:

How do I insert a row which contains a foreign key?

There is a tiny chance for a race condition under heavy concurrent load. (Doing everything in a single statement minimizes the time window.) If that's the case:

Is SELECT or INSERT in a function prone to race conditions?

Postgresql – join value in a RETURNING which is not inserted

Perhaps there's a better alternative but I can only think of joining back to the 2 tables.

This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:

with ins (id, x, y) as
( insert into new_table (x, y) 
  select A.x, B.y 
  from A join B on A.w = B.z 
  returning id, x, y
)
-- insert into another_table (id, z)
select 
    ins.id, B.z        -- whatever columns from the 3 tables
from ins 
  join A on A.x = ins.x 
  join B on B.y = ins.y and A.w = B.z ;

Best Answer

Related Solutions

Sql-server – Insert New Foreign Key Row for new Strings

Postgresql – join value in a RETURNING which is not inserted

Related Question