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?