防止在数据库中使用重复的标签

Question

我想知道如何防止在数据库表中使用两个相同的标签。有人说我在表中使用两个私钥。但是，W3Schools-网站表示这是不可能的。

Answer 1

我已经更新了NORMA模型，使其与您的图表更加匹配。我可以看到您在哪里犯了一些错误，但是其中一些错误可能是由于我的早期html" target="_blank">模型造成的。

我已更新此模型以防止重复标签。以前真的没关系。但是，既然您想要它，这里就是（对于Postgres）：

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

CREATE SCHEMA so;

SET search_path TO SO,"$user",public;

CREATE DOMAIN so.HashedPassword AS 
    BIGINT CONSTRAINT HashedPassword_Unsigned_Chk CHECK (VALUE >= 0);

CREATE TABLE so."User"
(
    USER_ID SERIAL NOT NULL,
    USER_NAME CHARACTER VARYING(50) NOT NULL,
    EMAIL_ADDRESS CHARACTER VARYING(256) NOT NULL,
    HASHED_PASSWORD so.HashedPassword NOT NULL,
    OPEN_ID CHARACTER VARYING(512),
    A_MODERATOR BOOLEAN,
    LOGGED_IN BOOLEAN,
    HAS_BEEN_SENT_A_MODERATOR_MESSAGE BOOLEAN,
    CONSTRAINT User_PK PRIMARY KEY(USER_ID)
);

CREATE TABLE so.Question
(
    QUESTION_ID SERIAL NOT NULL,
    TITLE CHARACTER VARYING(256) NOT NULL,
    WAS_SENT_AT_TIME TIMESTAMP NOT NULL,
    BODY CHARACTER VARYING NOT NULL,
    USER_ID INTEGER NOT NULL,
    FLAGGED_FOR_MODERATOR_REMOVAL BOOLEAN,
    WAS_LAST_CHECKED_BY_MODERATOR_AT_TIME TIMESTAMP,
    CONSTRAINT Question_PK PRIMARY KEY(QUESTION_ID)
);

CREATE TABLE so.Tag
(
    TAG_ID SERIAL NOT NULL,
    TAG_NAME CHARACTER VARYING(20) NOT NULL,
    CONSTRAINT Tag_PK PRIMARY KEY(TAG_ID),
    CONSTRAINT Tag_UC UNIQUE(TAG_NAME)
);

CREATE TABLE so.QuestionTaggedTag
(
    QUESTION_ID INTEGER NOT NULL,
    TAG_ID INTEGER NOT NULL,
    CONSTRAINT QuestionTaggedTag_PK PRIMARY KEY(QUESTION_ID, TAG_ID)
);

CREATE TABLE so.Answer
(
    ANSWER_ID SERIAL NOT NULL,
    BODY CHARACTER VARYING NOT NULL,
    USER_ID INTEGER NOT NULL,
    QUESTION_ID INTEGER NOT NULL,
    CONSTRAINT Answer_PK PRIMARY KEY(ANSWER_ID)
);

ALTER TABLE so.Question 
    ADD CONSTRAINT Question_FK FOREIGN KEY (USER_ID) 
    REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.QuestionTaggedTag 
    ADD CONSTRAINT QuestionTaggedTag_FK1 FOREIGN KEY (QUESTION_ID) 
    REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.QuestionTaggedTag 
    ADD CONSTRAINT QuestionTaggedTag_FK2 FOREIGN KEY (TAG_ID) 
    REFERENCES so.Tag (TAG_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.Answer 
    ADD CONSTRAINT Answer_FK1 FOREIGN KEY (USER_ID) 
    REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.Answer 
    ADD CONSTRAINT Answer_FK2 FOREIGN KEY (QUESTION_ID) 
    REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

COMMIT WORK;

请注意，现在有一个单独的Tag表TAG_ID作为主键。TAG_NAME是一个具有唯一性约束的单独列，可防止重复的标签。该QuestionTaggedTag表现在具有（QUESTION_ID，TAG_ID），这也是它的主键。

我希望我不会在回答这个问题上走得太远，但是当我尝试编写较小的答案时，我一直不得不解开我以前的答案，而仅仅发布它似乎更简单。