Foreign key contraints in many-to-many relationships

One way to achieve the behaviour you seek (delete unused labels from the database) would be to use triggers.

You could try writing something like:

CREATE OR REPLACE TRIGGER tr_LabelPosts_chk_no_more_associated_posts 
AFTER DELETE ON LabelPosts 
FOR EACH ROW 
EXECUTE PROCEDURE f_LabelPosts_chk_no_more_associated_posts();


CREATE OR REPLACE FUNCTION f_LabelPosts_chk_no_more_associated_posts() 
RETURNS TRIGGER AS $$
DECLARE
    var_associated_post_count INTEGER;
BEGIN
    SELECT Count(*) AS associated_post_count INTO var_associated_post_count FROM LabelPosts WHERE labelId = OLD.labelId;
    IF(var_associated_post_count = 0) THEN
        DELETE FROM Labels WHERE labelId = OLD.labelId;
    END IF;
END
$$ LANGUAGE 'plpgsql';

Basically, what happens here is:

1. A row is deleted from table Posts.
2. The deletion is cascaded to all associated rows in LabelPosts (thanks to your foreign key constraint).
3. After the deletion of every single row in LabelPosts the trigger is activated, which in turn calls the PostgreSQL function.
4. The function checks whether there are any other posts connected with the labelId in question. If so, then it finishes without any further modification. However, if there aren't any other rows in the relationship table, then the label is not used elsewhere and can thus be deleted.
5. The function executes a delete DML on the Labels table, effectively removing the (now) unused label.

Obviously the naming isn't the best and there must be a ton of syntax errors in there, so see here and here for more information. There may be better ways to taking this thing down, however at the moment I can't think of a fast method that would not destroy the nice generic-looking table structure.

Although bare in mind - it is not generally a good practice to overburden your database with triggers. It makes every associated query/statement run a tat slower & also makes administration considerably more difficult. (Sometimes you need to disable triggers to perform certain DML operations, etc. depending on the nature of your triggers).

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.