To prevent the use of duplicate Tags in a database

问题:

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?

回答1:

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.

回答2:

You can create a unique constraint on (question_id, tag_name) in the tags table, which will ensure that the pair is unique. That would mean that the same question may not have the same tag attached more than once. However, the same tag could still apply to different questions.

回答3:

You cannot create two primary keys, but you can place a uniqueness constraint on an index.

回答4:

You can only have one primary key (I assume that's what you mean by "private" key), but that key can be a composite key consisting of the question-id and tag-name. In SQL, it would look like (depending on your SQL dialect):

CREATE TABLE Tags
(
  question_id int,
  tag_name varchar(xxx),
  PRIMARY KEY (question_id, tag_name)
);

This will ensure you cannot have the same tag against the same question.

回答5:

I will use PostgreSQL or Oracle.

I feel that the following is correspondent to Ken's code which is for MySQL.

CREATE TABLE Tags 
     (
         QUESTION_ID integer FOREIGN KEY REFERENCES Questions(QUESTION_ID) 
                             CHECK (QUESTION_ID>0), 
         TAG_NAME nvarchar(20) NOT NULL,
         CONSTRAINT no_duplicate_tag UNIQUE (QUESTION_ID,TAG_NAME)
     )

I added some extra measures to the query. For instance, CHECK (USER_ID>0) is to ensure that there is no corrupted data in the database.

I dropped out the AUTO_INCREMENT from this QUESTION_ID because I see that it would break our system, since one question cannot then have two purposely-selected tags. In other, tags would go mixed up.

I see that we need to give a name for the constraint. Its name is no_duplicate_tag in the command.