Contexto

Estamos construindo um blog para uma introdução. para o projeto do curso de bases de dados.

Em nosso blog, queremos ser capazes de definirLabels emPosts. oLabels não pode existir por si só, eles só o fazem se eles estão relacionados a umPosts. Deste jeito,Labels que não são usados ​​por nenhumPosts não deve ficar no banco de dados.

Mais de umLabel pode pertencer a um únicoPoste mais que um únicoPost pode usar umLabel.

Estamos usando o SQLite3 (localmente / testing) e o PostgreSQL (deployment).

Implementação

Aqui está o SQL (sabor do SQLite3) que usamos para criar essas duas tabelas, junto com a tabela de relacionamento:

Postagens

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
)

Rótulos

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 (relação entrePost [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
)

Problema

Usando o SQLite3,Labels não são excluídos do banco de dados quando eu removo todas as referências a ele doLabelPosts mesa. Eu acho que pelo motivo dado pelo Postgres, apesar do SQLite aceitar a tabela sem aviso prévio.

O PostgreSQL reclama quelabelId não é único dentroLabelPosts, o que é verdadeiro e também obrigatório, já que é muitos para muitos:

pq: S: "ERRO" R: "transformFkeyCheckAttrs" L: "6511" C: "42830" F: "tablecmds.c"
M: "não existe uma restrição única que corresponda às chaves dadas pela tabela referenciada \" labelposts \ ""

Então eu entendo que estou fazendo minha restrição errada. No entanto, não sei como fazê-lo corretamente.