PostgreSQL Question posted in
The official documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    I can think of multiple ways of doing this:

    1. Invert the relationship if it makes sense so that Files reference Calls.
    2. Use triggers
    3. Just delete a file instead of the call and the file then will cascade to the call:
    delete from files where id = (select file_id from calls where id = :call)
    1. And if you don’t want any cascades at all, you can just write multiple statements using a CTE (common table expression):
    with deleted_calls as (
  delete from calls where id = :call
  returning (file_id)
)
delete from files where id = (select file_id from deleted_calls);

    Note, that there are 2 queries to calls table. If we didn’t have a FK, we could’ve first deleted the

    Login or Signup to reply.
  2. 0

    I would choose the simple way:

    CREATE TABLE calls
(
    call_id SERIAL PRIMARY KEY
    /* other fields */
);

CREATE TABLE files
(
    file_id SERIAL PRIMARY KEY,
    call_id INT UNIQUE REFERENCES calls (call_id) ON DELETE CASCADE
    /* other fields */
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search