Insert New Foreign Key Row for new Strings

Question

Lets say I have a table new_data like this:

ndx    | someval | somemachine
serial | integer | integer
----------------------------

And let's say I have a reference table new_data_someval_ref:

someval | someval_str
integer | varchar
---------------------

(Where, obviously, new_data_someval_ref.someval is foreign key on new_data.someval)

And I want do inserts like so:

INSERT INTO new_data
  (someval, machine)
VALUES (
  (SELECT someval
   FROM new_data_someval_ref
   WHERE someval_str = ?),
  ?)

BUT, if there is no match in the reference table, I would like to add a row containing the new string and an incremented someval, then do the insert above.

So my question is there clean, possibly even constraint-based way to do this?

(I use both Postgres and SQL Server regularly, so an answer addressing both would be ideal, but not necessary.)

score 2 · Answer 1 · edited May 23 '17 at 12:40

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?

score 1 · Answer 2 · edited Oct 29 '14 at 02:02

I think you will have to break it into 2 blocks (stored procedure would be easier)

DECLARE @SomeVal INT 

SET @SomeVal = Isnull((SELECT someval 
                       FROM   new_data_someval_ref 
                       WHERE  someval_str = 'WhatEverTheValueIs'), 0) 

IF @SomeVal = 0 
  BEGIN 
      INSERT INTO new_data_someval_ref 
                  (someval_str) 
      VALUES      ('WhatEverTheValueIs') 

      SET @SomeVal = Scope_identity() 
  END 

INSERT INTO new_data
                 (someval, machine) 
VALUES           (@SomeVal, ?)

Insert New Foreign Key Row for new Strings

2 Answers2