PostgreSQL zero-or-one to zero-or-one relationship

As the title describes I want to have a zero-or-one to zero-or-one relationship.
For example, let’s say we have two tables Calls and Files. A call might have an attached file on it, and vice versa. But I have a restriction. I want if I delete a call, its file be deleted too, but if I delete its file call should be remain as it is. Would something like this be possible with SQL Constraints (like ON DELETE CASCADE / ON DELETE SET NULL)? Or is there any way, implement such a behavior in database with SQL, without using trigger/events?

I tried something like below:

-- Create the 'files' table
CREATE TABLE files (
    id SERIAL PRIMARY KEY,  -- Primary key for the 'files' table
    file_name TEXT NOT NULL -- Any additional fields for the 'files' table
);

-- Create the 'calls' table
CREATE TABLE calls (
    id SERIAL PRIMARY KEY,  -- Primary key for the 'calls' table
    file_id INT UNIQUE,     -- Foreign key referencing 'files' table (1-1 relationship)
    call_description TEXT,  -- Any additional fields for the 'calls' table
    CONSTRAINT fk_file_id FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE SET NULL
);

-- Add an additional constraint on the 'files' table to enforce the cascading behavior
ALTER TABLE files
ADD CONSTRAINT fk_call_file
FOREIGN KEY (id)
REFERENCES calls(file_id)
ON DELETE CASCADE;

but it requires to be deferrable as constraints are executed immediately. Also, it is not what I was intended. I want a call/file to exists without dependencies to each other.

What are the best practices to follow in such case?

You need to sign in to view this answers

Related Post