postgresql transactions violates foreign key constraint

Viewed 137

I have the following nodejs code (simplified):

// tables

CREATE TABLE admins (
    admin_id bigint DEFAULT nextval('admins_seq') NOT NULL,
    username character varying(25) NOT NULL,
    password character varying(150) NOT NULL
);
CREATE TABLE admin_notifications (
    admin_notification_id bigint DEFAULT nextval('admin_notifications_seq') NOT NULL,
    admin_id bigint NOT NULL,
    type character varying(150) NOT NULL
);
ALTER TABLE admin_notifications ADD CONSTRAINT admin_notifications_to_admins_fk FOREIGN KEY (admin_id) REFERENCES admins(admin_id) ON DELETE CASCADE;


await client.query('BEGIN');

// create a new array in the database and return the newly created admin id
const query = 'INSERT INTO admins (username, password) VALUES($1,$2) RETURNING admin_id';
const values = [];
const result = await client.query(query, values);

// insert a welcome notification for the newly created admin
const query2 = 'INSERT INTO admin_notifications (admin_id, type) VALUES ($1,$2)';
const values2 = [result.rows[0].admin_id, 'welcome'];
const result2 = await client.query(query2, values2);

await client.query('COMMIT');

A little explanation: i create a new admin in the table admins. When a new admin is created, I want to insert a new welcome notification in the table admin_notifications. The column admin_id in the table admin_notifications is a foreign key to the table admins (admin_id).

I get the following error:

Error: insert or update on table "admin_notifications" violates foreign key constraint "admin_notifications_to_admins_fk"

When I leave out the transactions it works?!

Does it has something to do with deferring constraints? And how to handle this in my code?

0 Answers
Related