防止在数据库中使用重复的标记 [英] To prevent the use of duplicate Tags in a database

问题描述

我想知道如何防止在数据库表中使用两个相同的标记。
一个说我在表中使用两个私钥。

> alt text http://files.getdropbox.com/u/175564/db /db7.png

我的逻辑表

alt text http://files.getdropbox.com/u/175564/db/db77。 png

表格上下文

alt text http://files.getdropbox.com/u/175564/db/db777.png

如何防止在问题中使用重复的标签？

解决方案

我更新了我的NORMA模型，以更接近地匹配你的图表。我可以看到你犯了一些错误，但其中一些可能是由于我早期的模型。

我更新了这个模型，以防止重复的标签。它以前没有真的重要。但是因为你想要它，这里是（对于Postgres）：

 开始事务隔离级别可读， 
 
 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 ），
 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）
引用User（USER_ID）ON DELETE RESTRICT ON UPDATE RESTRICT; 
 
 ALTER TABLE so.Answer 
 ADD CONSTRAINT Answer_FK2 FOREIGN KEY（QUESTION_ID）
参考文献：QUESTION_ID ON DELETE RESTRICT ON UPDATE RESTRICT; 
 
 COMMIT WORK; 
  

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

我希望我没有走得太远回答这个，但是当我试图写较小的答案，我不得不解开我的早期的答案，它似乎更简单只是发布这个。

I would like to know how you can prevent to use of two same tags in a database table. One said me that use two private keys in a table. However, W3Schools -website says that it is impossible.

My relational table

alt text http://files.getdropbox.com/u/175564/db/db7.png

My logical table

alt text http://files.getdropbox.com/u/175564/db/db77.png

The context of tables

alt text http://files.getdropbox.com/u/175564/db/db777.png

How can you prevent the use of duplicate tags in a question?

解决方案

I have updated my NORMA model to more closely match your diagram. I can see where you've made a few mistakes, but some of them may have been due to my earlier model.

I have updated this model to prevent duplicate tags. It didn't really matter before. But since you want it, here it is (for 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;

Note that there is now a separate Tag table with TAG_ID as the primary key. TAG_NAME is a separate column with a uniqueness constraint over it, preventing duplicate tags. The QuestionTaggedTag table now has (QUESTION_ID, TAG_ID), which is also its primary key.

I hope I didn't go too far in answering this, but when I tried to write smaller answers, I kept having to untangle my earlier answers, and it seemed simpler just to post this.

这篇关于防止在数据库中使用重复的标记的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！