为了少在Client做DB的逻辑判断,可以尽量用DB的Union 约束来限制数据的重复输入,
那么怎么建立Group_users表中GroupID,userId的Union 约束呢? 如下
DROP TABLE USER_GROUP CASCADE CONSTRAINTS ;
CREATE TABLE USER_GROUP (
ID NUMBER NOT NULL,
USER_ID NUMBER (8) DEFAULT 0 NOT NULL,
GROUP_ID NUMBER (8) DEFAULT 0 NOT NULL,
CONSTRAINT PK_USERCPHASE
PRIMARY KEY ( ID )
USING INDEX
TABLESPACE NEWKM_IDX PCTFREE 10
STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 ))
TABLESPACE NEWKM_DAT
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
ALTER TABLE USER_GROUP ADD CONSTRAINT FK_USER_GRO_REFERENCE_GROUPS
FOREIGN KEY (GROUP_ID)
REFERENCES NEWKM.GROUPS (ID) ;
ALTER TABLE USER_GROUP ADD CONSTRAINT FK_USER_GRO_REFERENCE_USERS
FOREIGN KEY (USER_ID)
REFERENCES NEWKM.USERS (ID) ;
CREATE UNIQUE INDEX IX_USERGROUP_1 ON
"NEWKM".USER_GROUP(USER_ID, GROUP_ID)
TABLESPACE NEWKM_IDX PCTFREE 10 STORAGE(INITIAL 65536 NEXT 1048576 PCTINCREASE 0 )
;
GRANT SELECT ON USER_GROUP TO NEWEPP;
COMMENT ON TABLE USER_GROUP IS '群組人員檔(USER_GROUP)';
COMMENT ON COLUMN USER_GROUP.GROUP_ID IS '群組名稱序號';
COMMENT ON COLUMN USER_GROUP.ID IS '序號';
COMMENT ON COLUMN USER_GROUP.USER_ID IS '人員序號';