E
element
Guten Tag zusammen,
Ich habe folgendes Problem:
Ich möchte über mehrere DB2 Tabellen eine Userverwaltung mir folgendem SQL aufbauen:
Nun habe ich folgendes Problem:
Wenn ich in der "USERS" eine Datensatz einfüge mit einem normalen "INSERT INTO ...." kann ich diesen nicht mehr löschen.
Kann mir da vllt. jemand helfen?
mfg element
Ich habe folgendes Problem:
Ich möchte über mehrere DB2 Tabellen eine Userverwaltung mir folgendem SQL aufbauen:
Code:
CREATE TABLE USERS (
U_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
USERNAME VARCHAR(10) NOT NULL,
NAME VARCHAR(64) NOT NULL,
LOCKED INTEGER NOT NULL
);
CREATE TABLE COMPS (
C_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
COMP VARCHAR(3) NOT NULL,
COMPNAME VARCHAR(64) NOT NULL
);
CREATE TABLE GROUPS (
G_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
NAME VARCHAR(64) NOT NULL
);
CREATE TABLE PGMS (
P_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
CLASS VARCHAR(64) NOT NULL,
NAME VARCHAR(64) NOT NULL,
DIRECTORY VARCHAR(128) NOT NULL,
DESCRIPTION VARCHAR(256) NOT NULL,
PGMCALL VARCHAR(64) NOT NULL,
LOCKED INTEGER NOT NULL
);
CREATE TABLE GROUPPGMS (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
P_ID INTEGER NOT NULL,
G_ID INTEGER NOT NULL,
LOCKED INTEGER NOT NULL,
RW INTEGER NOT NULL,
CONSTRAINT GROUPPGMS_P_ID FOREIGN KEY (P_ID) REFERENCES PGMS (P_ID) ON DELETE CASCADE,
CONSTRAINT GROUPPGMS_G_ID FOREIGN KEY (G_ID) REFERENCES GROUPS (G_ID) ON DELETE CASCADE
);
CREATE TABLE GRPCOMP (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
G_ID INTEGER NOT NULL,
C_ID INTEGER NOT NULL,
CONSTRAINT GRPCOMP_G_ID FOREIGN KEY (G_ID) REFERENCES GROUPS (G_ID) ON DELETE CASCADE,
CONSTRAINT GRPCOMP_C_ID FOREIGN KEY (C_ID) REFERENCES COMPS (C_ID) ON DELETE CASCADE
);
CREATE TABLE USERCOMP (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
C_ID INTEGER NOT NULL,
U_ID INTEGER NOT NULL,
CONSTRAINT USERCOMP_C_ID FOREIGN KEY (C_ID) REFERENCES COMPS (C_ID) ON DELETE CASCADE,
CONSTRAINT USERCOMP_U_ID FOREIGN KEY (U_ID) REFERENCES USERS (U_ID) ON DELETE CASCADE
);
CREATE TABLE USERGRPS (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
U_ID INTEGER NOT NULL,
G_ID INTEGER NOT NULL,
CONSTRAINT USERGRPS_U_ID FOREIGN KEY (U_ID) REFERENCES USERS (U_ID) ON DELETE CASCADE,
CONSTRAINT USERGRPS_G_ID FOREIGN KEY (G_ID) REFERENCES GROUPS (G_ID) ON DELETE CASCADE
);
CREATE TABLE USERPGMS (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) PRIMARY KEY ,
P_ID INTEGER NOT NULL,
U_ID INTEGER NOT NULL,
ACCESS INTEGER NOT NULL,
RW INTEGER NOT NULL,
CONSTRAINT USERPGMS_P_ID FOREIGN KEY (P_ID) REFERENCES PGMS (P_ID) ON DELETE CASCADE,
CONSTRAINT USERPGMS_U_ID FOREIGN KEY (U_ID) REFERENCES USERS (U_ID) ON DELETE CASCADE
);
Nun habe ich folgendes Problem:
Wenn ich in der "USERS" eine Datensatz einfüge mit einem normalen "INSERT INTO ...." kann ich diesen nicht mehr löschen.
Kann mir da vllt. jemand helfen?
mfg element