sqldatabasepostgresqlforeign-keysrelationship

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?


Solution

  • 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 */
    );
    

    The calls table does not contain a reference to the files table. A row in the files table represents a file. If you delete a row in the calls table, the associated file will be automatically deleted. If you delete the file row, it will not affect the associated call row.

    The UNIQUE constraint for call_id in files table eliminates the situation when several files are associated with one call.