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.)
someval_str
is, but it is certainly not an integer. Probably some actualCREATE TABLE
statements would help.CREATE TABLE
would be much more understandable...new_data.someval
is a foreign key onnew_data_someval_ref.someval
. To avoid such ambiguities, it is always better to provide completeCREATE TABLE
scripts.