Question

我需要一些帮助了解外国钥匙和连锁店是如何运作的。我理解这一理论,但我有麻烦,难以把这些理论应用到真正的世界范例。

请允许我假设以下表格(以及可参考表格<代码>的编号/代码>的任意数目):

CREATE TABLE tags (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) UNIQUE
) Engine=InnoDB;

CREATE TABLE news (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(63),
    content TEXT,
    INDEX (title)
) Engine=InnoDB;

因此,我再造一个表格,以提供<条码>、和<条码>之间的许多对手关系:

CREATE TABLE news_tags (
    news_id INT UNSIGNED,
    tags_id INT UNSIGNED,
    FOREIGN KEY (news_id) REFERENCES news (id) ON DELETE ...,
    FOREIGN KEY (tags_id) REFERENCES tags (id) ON DELETE ...
) Engine=InnoDB;

我对学院的要求:

If I delete a news, all corresponding entries in news_tags should be removed as well.
Same applies for table x that may be added later with x_tags-table.
If I delete a tag, all corresponding entries in news_tags and in every further table x_tags should be removed as well.

我恐怕我可能不得不为此目的重新讨论我的桌子结构,但那是荒谬的,因为我只想说什么是徒劳的。

赞赏与良好教学、地理结构或JPA-examples的任何联系!

Answer 1

你似乎提出这样的建议,这对我来说是合理的:

CREATE TABLE tags 
(
 id INTEGER NOT NULL, 
 name VARCHAR(20) NOT NULL, 
 UNIQUE (id), 
 UNIQUE (name)
);

CREATE TABLE news 
(
 id INTEGER NOT NULL, 
 title VARCHAR(30) NOT NULL,
 content VARCHAR(200) NOT NULL,
 UNIQUE (id)
);

CREATE TABLE news_tags
(
 news_id INTEGER NOT NULL, 
 tags_id INTEGER NOT NULL, 
 UNIQUE (tags_id, news_id), 
 FOREIGN KEY (news_id) 
    REFERENCES news (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 FOREIGN KEY (tags_id) 
    REFERENCES tags (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

友情链接