Create foreign key row if it doesn't exist yet when inserting row referencing it-CodePudding

I have a table of users and a table of values which have a foreign key to the users table. I want to insert values into the values table which either reference an existing user or create one. I'm using PostgreSQL 12.

The tables are:

CREATE TABLE users (
    id SERIAL NOT NULL, 
    tenant TEXT NOT NULL, 
    name TEXT NOT NULL, 
    PRIMARY KEY (id), 
    UNIQUE (tenant, name), 
)

CREATE TABLE values (
    user_id INTEGER NOT NULL, 
    value TEXT NOT NULL, 
    UNIQUE (user_id, value), 
    FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
)

Given a tenant, name and value, how can I insert that into the values table in ideally a single query or at least in an atomic way?

CodePudding user response：

I think you in this situation you should insert the user first into the users tables ,then insert a value of it like that

insert into users (1,'me','sohaib')
insert into values (1,'sohaib is the my first name')

Because you can't do it in one query .

CodePudding user response：

To do this atomically, you need to wrap your insert statements in a transaction block.

    BEGIN;

    insert into users (1, "tenant", "name");
    insert into values (1, "value");

    COMMIT;