创建角色表

CREATE TABLE NBCTXP.TBL_NBC_NONBANKROLE (
    ID BIGINT NOT NULL,
    ROLENAME VARCHAR(50),
    CREATETIME TIMESTAMP,
    UPDATETIME TIMESTAMP,
    CONSTRAINT P_ID PRIMARY KEY (ID)
);

CREATE INDEX SQL150130091455900 ON NBCTXP.TBL_NBC_NONBANKROLE (ID);

让主键递增1
Alter table NBCTXP.TBL_NBC_NONBANKROLE alter column ID set generated always as identity (start with 1,increment by 1)

 

 

 

创建角色表和用户表的中间表

CREATE TABLE NBCTXP.TBL_NBC_NONBANKUR (
    USERID BIGINT NOT NULL,
    ROLEID BIGINT NOT NULL,
    CONSTRAINT PK_UR PRIMARY KEY (USERID,ROLEID),
    CONSTRAINT FK_UID FOREIGN KEY (USERID) REFERENCES NBCTXP.TBL_NBC_NONBANKUSER(ID),
    CONSTRAINT FK_RID FOREIGN KEY (ROLEID) REFERENCES NBCTXP.TBL_NBC_NONBANKROLE(ID)
);

CREATE INDEX PK_UR ON NBCTXP.TBL_NBC_NONBANKUR (USERID,ROLEID);