Re: Simple Atomic Relationship Insert

On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
wrote:

> Let's say I have two tables like this (I'm leaving stuff out for
> simplicity):
>
> CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE hometowns (
> id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
> name VARCHAR,
> PRIMARY KEY (id),
> UNIQUE(name)
> );
>
> CREATE SEQUENCE USER_SEQ_GEN START 1 INCREMENT 1;
> CREATE TABLE users (
> id BIGINT DEFAULT nextval('USER_SEQ_GEN'),
> hometown_id INTEGER,
> name VARCHAR NOT NULL,
> PRIMARY KEY (id),
> FOREIGN KEY (hometown_id) REFERENCES hometowns(id)
> );
>
> The hometowns table is populate as users are created. For example, a
> client may submit {"name":"Robert", "hometown":"Portland"}.
>
> The hometowns table will never be updated, only either queries or inserted.
>
> So given this I need to INSERT a row into "users" and either SELECT the
> hometowns.id that matches "Portland" or if it doesn't exist I INSERT it
> returning the hometowns.id".
>
> Normally I would do by first doing a SELECT on hometown. If I don't get
> anything I do an INSERT into hometown RETURNING the id. If THAT throws an
> error then I do the SELECT again. Now I'm finally ready to INSERT into
> users using the hometowns.id from the above steps.
>
> But wow, that seems like a lot of code for a simple "Add if doesn't exist"
> foreign key relationship -- but this is how I've always done.
>
> So my question. Is there a simpler, more performant, or thread-safe way to
> do this?
>
> Thanks!
>
>
​What occurs to me is to simply do an INSERT into the "hometowns" table and
just ignore the "already exists" return indication. Then do a SELECT to get
the hometowns​ id which now exists, then INSERT the users. but I could
easily be overlooking some reason why this wouldn't work properly.

--
​
While a transcendent vocabulary is laudable, one must be eternally careful
so that the calculated objective of communication does not become ensconced
in obscurity. In other words, eschew obfuscation.

111,111,111 x 111,111,111 = 12,345,678,987,654,321

Maranatha! <><
John McKown