Foreign key contraints in many-to-many relationships

Question

Context

We're building a blog for an intro. to databases course project.

In our blog, we want to be able to set Labels on Posts. The Labels can't exist by themselves, they only do so if they are related to a Posts. This way, Labels that are not used by any Posts shouldn't stay in the database.

More than one Label can belong to a single Post, and more than a single Post can use a Label.

We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).

Implementation

Here is the SQL (SQLite3 flavor) that we use to create those two tables, along with the relationship table:

Posts

CREATE TABLE IF NOT EXISTS Posts(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   authorId INTEGER,
   title VARCHAR(255),
   content TEXT,
   imageURL VARCHAR(255),
   date DATETIME,
   FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE SET NULL
)

Labels

CREATE TABLE IF NOT EXISTS Labels(
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name VARCHAR(255) UNIQUE,
   -- This is not working:
   FOREIGN KEY (id) REFERENCES LabelPosts(labelId) ON DELETE CASCADE 
)

LabelPosts (relation between Post [1..*] -- * Label)

CREATE TABLE IF NOT EXISTS LabelPosts(
    postId INTEGER,
    labelId INTEGER,
    PRIMARY KEY (postId, labelId),
    FOREIGN KEY (postId) REFERENCES Posts(id) ON DELETE CASCADE
)

Problem

• Using SQLite3, Labels are not deleted from the database when I remove all references to it from the LabelPosts table. I think for the reason given by Postgres, despite SQLite accepting the table without warning.

• PostgreSQL complains that labelId is not unique within LabelPosts, which is true and also required, since it's many-to-many:

pq: S:"ERROR" R:"transformFkeyCheckAttrs" L:"6511" C:"42830" F:"tablecmds.c"
M:"there is no unique constraint matching given keys for referenced table \"labelposts\""

So I understand that I'm doing my constraint wrong. However I don't know how to do it properly.

Answer 1

We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).

This is begging for trouble. You will keep running into minor incompatibilities. Or not even notice them until much later, when damage is done. Don't do it. Use PostgreSQL locally, too. It's freely available for most every OS. For someone involved in a "databases course project" this is a surprising folly. Related:

• Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

Other advice:

• As @Priidu mentioned in the comments, your foreign key constraints are backwards. This is not up for debate, they are simply wrong.

• In PostgreSQL use a serial or IDENTITY column (Postgres 10+) instead of SQLite AUTOINCREMENT. See:

  • Auto increment table column

• Use timestamp (or timestamptz) instead of datetime.

• Don't use mixed case identifiers.

  • PostgreSQL Trigger Exception

  • Are PostgreSQL column names case-sensitive?

• Don't use non-descriptive column names like id. Ever. That's an anti-pattern introduced by half-wit middleware and ORMs. When you join a couple of tables you end up with multiple columns of the name id. That's actively hurtful.

• There are many naming styles, but most agree it's better to have singular terms as table names. It's shorter and at least as intuitive / logical. label, not labels.

Everything put together, it could look like this:

CREATE TABLE IF NOT EXISTS post (
   post_id   serial PRIMARY KEY
 , author_id integer
 , title     text
 , content   text
 , image_url text
 , date      timestamp
);

CREATE TABLE IF NOT EXISTS label (
   label_id  serial PRIMARY KEY
 , name      text UNIQUE
);

CREATE TABLE IF NOT EXISTS label_post(
    post_id  integer REFERENCES post(post_id) ON UPDATE CASCADE ON DELETE CASCADE
  , label_id integer REFERENCES label(label_id) ON UPDATE CASCADE ON DELETE CASCADE
  , PRIMARY KEY (post_id, label_id)
);

Trigger

To delete unused labels, implement a trigger. I supply another version since I am not happy with the one provided by @Priidu:

CREATE OR REPLACE FUNCTION f_trg_kill_orphaned_label() 
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM label l
   WHERE  l.label_id = OLD.label_id
   AND    NOT EXISTS (
      SELECT 1 FROM label_post lp
      WHERE  lp.label_id = OLD.label_id
      );
END
$func$;

• The trigger function must be created before the trigger.

• A simple DELETE command can do the job. No second query needed - in particular no count(*). EXISTS is cheaper.

• Single-quotes around the language name are tolerated, but it's an identifier really, so just omit the nonsense: LANGUAGE plpgsql

CREATE TRIGGER label_post_delaft_kill_orphaned_label
AFTER DELETE ON label_post
FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label();

There is no CREATE OR REPLACE TRIGGER in PostgreSQL, yet. Just CREATE TRIGGER.