|
我想知道如何防止在数据库表中使用两个相同的标签。有人说我在表中使用两个私钥。但是,W3Schools-网站表示这是不可能的。2 d3 r1 ?: o) _! L I+ @% y
7 U6 s: f |5 T2 h/ Y
解决方案: O: {. n$ }+ c V3 Q) r |" Z$ V& w
: d. R) u' Y# t) N8 _& I! g/ k2 g5 i# s4 m1 Q1 d J' M: N: k/ p3 F/ o
- ~* ~9 e# h) w6 f. I
我已经更新了NORMA模型,使其与您的图表更加匹配。我可以看到您在哪里犯了一些错误,但是其中一些错误可能是由于我的早期模型造成的。; \) R7 J. Z+ Z$ j2 D5 H
我已更新此模型以防止重复标签。以前真的没关系。但是,既然您想要它,这里就是(对于Postgres):
4 l. R7 Z0 S# D- j8 @/ VSTART TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;9 a- T0 m; g2 l- M' B
CREATE SCHEMA so;
, c; j$ U* C: g) J: wSET search_path TO SO,"$user",public;& W) [; }: f- t5 O: y P8 R( |
CREATE DOMAIN so.HashedPassword AS
n$ v# }( b0 d5 |: p" ] BIGINT CONSTRAINT HashedPassword_Unsigned_Chk CHECK (VALUE >= 0);! I# W1 R0 F9 t4 ]
CREATE TABLE so."User"
* P# C' a4 X# s; y(
( z) h4 [3 r8 Q3 u USER_ID SERIAL NOT NULL,+ H$ T P4 k: R2 j0 K. Z) W7 G' N- g
USER_NAME CHARACTER VARYING(50) NOT NULL,4 \! p4 m s* S7 E( l6 g2 Q
EMAIL_ADDRESS CHARACTER VARYING(256) NOT NULL,: u. R. f/ w+ X' N1 }( X5 F2 w
HASHED_PASSWORD so.HashedPassword NOT NULL,
3 z* c9 z6 T2 s [4 r( ~0 Q OPEN_ID CHARACTER VARYING(512),
0 a9 R& [( @* ~& Z A_MODERATOR BOOLEAN,& |2 e$ c1 X4 }% h# b
LOGGED_IN BOOLEAN,3 y* ~+ j1 |) j2 ~" G. r, |
HAS_BEEN_SENT_A_MODERATOR_MESSAGE BOOLEAN,
* o8 J! f- Z/ J% v/ S CONSTRAINT User_PK PRIMARY KEY(USER_ID)& B* c: Y' d7 g0 A# ^( z
);
: _: h* Q: Z0 x A1 x; C' x8 YCREATE TABLE so.Question
) e: b5 i8 L9 y. L9 S(' K6 b0 f- N# }1 V3 @
QUESTION_ID SERIAL NOT NULL,; D2 O# u" U0 X, \) \! m
TITLE CHARACTER VARYING(256) NOT NULL,
# T4 x6 N. q: T, c WAS_SENT_AT_TIME TIMESTAMP NOT NULL," b" {9 K, g. x+ j
BODY CHARACTER VARYING NOT NULL,
/ H: T2 @$ V1 j3 r USER_ID INTEGER NOT NULL,
; h0 D' `$ j+ |$ d. R; Z FLAGGED_FOR_MODERATOR_REMOVAL BOOLEAN,
- x3 L8 m7 \ a# Y, p* ` WAS_LAST_CHECKED_BY_MODERATOR_AT_TIME TIMESTAMP,
5 ?' o* j/ {* Q% r. `8 _# R CONSTRAINT Question_PK PRIMARY KEY(QUESTION_ID)3 S6 t! b1 p& C& ?( w' |% `
);
" J% b% }6 Z: {( X* l; [CREATE TABLE so.Tag) _6 N) \; s% b. Q' s) `
(
! t: V2 h( M5 f! I TAG_ID SERIAL NOT NULL,# L3 }6 P$ n) D% S5 k! D
TAG_NAME CHARACTER VARYING(20) NOT NULL,
7 X1 _4 l# Q& o5 I3 Q0 U CONSTRAINT Tag_PK PRIMARY KEY(TAG_ID),
2 C8 g& q; P# T) f CONSTRAINT Tag_UC UNIQUE(TAG_NAME)4 H2 V# b0 y0 @8 \
);9 w1 @ C3 z9 ~) a( @
CREATE TABLE so.QuestionTaggedTag6 W' V* R0 p5 g% E* p" y/ J
(+ d6 z6 V8 I; n7 z! f
QUESTION_ID INTEGER NOT NULL,
! m+ _7 X2 _% m/ \ J TAG_ID INTEGER NOT NULL,* h$ A0 n! S1 @; V
CONSTRAINT QuestionTaggedTag_PK PRIMARY KEY(QUESTION_ID, TAG_ID)
; \4 d# j6 r) j O2 x& l) w/ n);# @% C" P3 d1 u5 L* ?
CREATE TABLE so.Answer! @4 b- S4 w# G( u3 R
(
( ?9 q% \8 P, c ANSWER_ID SERIAL NOT NULL,
& Z6 T) W) R" q' Q7 d T* w BODY CHARACTER VARYING NOT NULL,
8 |( ^- K4 `" r, X. B USER_ID INTEGER NOT NULL,4 @, H0 u3 w- ]; A1 s
QUESTION_ID INTEGER NOT NULL,
]4 o. `' h/ M$ |# _( A0 H6 b CONSTRAINT Answer_PK PRIMARY KEY(ANSWER_ID)
6 M2 J5 [+ [% t& C);- C( D9 c3 W* }- Y1 k' h+ J
ALTER TABLE so.Question
( A( s! F, Y1 a4 D% { ADD CONSTRAINT Question_FK FOREIGN KEY (USER_ID) ' B0 l% R9 Q3 q$ U5 L6 _* z
REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;2 X) R( z2 K) z- t6 l# c4 F
ALTER TABLE so.QuestionTaggedTag
6 ], Q) `: S$ s& n( [ ADD CONSTRAINT QuestionTaggedTag_FK1 FOREIGN KEY (QUESTION_ID) $ [4 g2 c' l$ o) Y) B
REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;
( i* B: K) N& G5 W2 B5 F9 jALTER TABLE so.QuestionTaggedTag & R% G8 \8 z2 ~/ V( F- [& u$ y
ADD CONSTRAINT QuestionTaggedTag_FK2 FOREIGN KEY (TAG_ID) ; s8 Q, h% v; f3 t- U' d- I4 V
REFERENCES so.Tag (TAG_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;0 i' k" ^; d0 X
ALTER TABLE so.Answer ( M1 @& l4 x& R$ o$ [7 G1 o i' B. f# X& {
ADD CONSTRAINT Answer_FK1 FOREIGN KEY (USER_ID)
8 ^7 I; H) t2 |0 A REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;) v) m7 b9 n( f q, }5 R G( ]
ALTER TABLE so.Answer
1 H7 b$ j% N3 a ADD CONSTRAINT Answer_FK2 FOREIGN KEY (QUESTION_ID) . Y2 ?9 e; J2 E4 V8 e- F! `2 P
REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;( S* K- {0 C. W2 C$ j$ Y, ? M
COMMIT WORK;
- t% z5 i8 A7 U- v请注意,现在有一个单独的Tag表TAG_ID作为主键。TAG_NAME是一个具有唯一性约束的单独列,可防止重复的标签。该QuestionTaggedTag表现在具有(QUESTION_ID,TAG_ID),这也是它的主键。
$ k$ R S$ f7 h我希望我不会在回答这个问题上走得太远,但是当我尝试编写较小的答案时,我一直不得不解开我以前的答案,而仅仅发布它似乎更简单。 |
|