|
Registriert seit: 7. Feb 2006
429 Beiträge
|
Re: warum definierte varchar-größe egal?
28. Mär 2006, 18:40
kein thema:
SQL-Code:
/******************************************************************************/
/**** Generated by IBExpert 2006.01.29 28.03.2006 18:39:58 ****/
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES UNICODE_FSS;
CREATE DATABASE ' C:\Dokumente und Einstellungen\Sancho\Eigene Dateien\DEMODEEN.GDB'
USER ' sysdba' PASSWORD ' masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UNICODE_FSS;
/******************************************************************************/
/**** Domains ****/
/******************************************************************************/
CREATE DOMAIN MEMOBLOB AS
BLOB SUB_TYPE 1 SEGMENT SIZE 4096;
CREATE DOMAIN VARCHAR_1 AS
VARCHAR(1);
CREATE DOMAIN VARCHAR_10 AS
VARCHAR(10);
CREATE DOMAIN VARCHAR_100 AS
VARCHAR(100);
CREATE DOMAIN VARCHAR_14 AS
VARCHAR(10);
CREATE DOMAIN VARCHAR_15 AS
VARCHAR(15);
CREATE DOMAIN VARCHAR_20 AS
VARCHAR(20);
CREATE DOMAIN VARCHAR_3 AS
VARCHAR(10);
CREATE DOMAIN VARCHAR_4 AS
VARCHAR(4);
CREATE DOMAIN VARCHAR_40 AS
VARCHAR(40);
CREATE DOMAIN VARCHAR_50 AS
VARCHAR(50);
CREATE DOMAIN VARCHAR_60 AS
VARCHAR(60);
CREATE DOMAIN VARCHAR_80 AS
VARCHAR(80);
/******************************************************************************/
/**** Generators ****/
/******************************************************************************/
CREATE GENERATOR GEN_AUT_ID;
SET GENERATOR GEN_AUT_ID TO 12;
CREATE GENERATOR GEN_DICENTRIES_ID;
SET GENERATOR GEN_DICENTRIES_ID TO 244;
CREATE GENERATOR GEN_DSKRPTLINK_DICENTRIES_ID;
SET GENERATOR GEN_DSKRPTLINK_DICENTRIES_ID TO 101;
CREATE GENERATOR GEN_DSKRPTLINK_D_LIT_ID;
SET GENERATOR GEN_DSKRPTLINK_D_LIT_ID TO 21;
CREATE GENERATOR GEN_DSKRPTS_ID;
SET GENERATOR GEN_DSKRPTS_ID TO 36;
CREATE GENERATOR GEN_D_LIT_ID;
SET GENERATOR GEN_D_LIT_ID TO 18;
CREATE GENERATOR GEN_LIT_ID;
SET GENERATOR GEN_LIT_ID TO 16;
CREATE GENERATOR GEN_TITELAUFS_LIT_ID;
SET GENERATOR GEN_TITELAUFS_LIT_ID TO 6;
CREATE GENERATOR GEN_TITELWERK_LIT_ID;
SET GENERATOR GEN_TITELWERK_LIT_ID TO 12;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE AUT (
ID INTEGER NOT NULL,
AUT VARCHAR_3,
VF VARCHAR_50,
FUNKTION VARCHAR_50,
FIRMA1 VARCHAR_50,
FIRMA2 VARCHAR_50,
STRASSE VARCHAR_40,
PLZ VARCHAR_10,
ORT VARCHAR_40,
LAND VARCHAR_20,
TEL VARCHAR_20,
TELPRIV VARCHAR_20,
FAX VARCHAR_20,
ANM VARCHAR_80,
DATUM DATE,
TYP VARCHAR_1
);
CREATE TABLE D_LIT (
ID INTEGER NOT NULL,
ID_LIT SMALLINT NOT NULL
);
CREATE TABLE DICENTRIES (
ID INTEGER NOT NULL,
ASTERM VARCHAR_80,
ASABK VARCHAR_10,
ASPRGM VARCHAR_20,
ASSEM VARCHAR_80,
ZSTERM VARCHAR_80,
ZSABK VARCHAR_10,
ZSPRGM VARCHAR_20,
ZSSEM VARCHAR_80,
DATUM DATE,
PROJ VARCHAR_20,
REV VARCHAR_1,
UPDDATUM DATE,
ASVERW INTEGER,
ZSVERW INTEGER,
ASDEF MEMOBLOB,
ZSDEF MEMOBLOB,
ASAUDIO VARCHAR_20,
ASVIDEO VARCHAR_20,
ASABBILDUNG VARCHAR_20,
ASQCODE INTEGER,
ZSQCODE INTEGER,
AUT INTEGER,
UPDAUT INTEGER,
ZSABBILDUNG VARCHAR_20,
ZSAUDIO VARCHAR_20,
ZSVIDEO VARCHAR_20
);
CREATE TABLE DSKRPTLINK_D_LIT (
ID INTEGER NOT NULL,
ID_D_LIT INTEGER NOT NULL,
ID_DSKRPT SMALLINT NOT NULL
);
CREATE TABLE DSKRPTLINK_DICENTRIES (
ID_DICENTRY INTEGER NOT NULL,
ID_DSKRPT INTEGER NOT NULL,
ID INTEGER NOT NULL
);
CREATE TABLE DSKRPTS (
ASDSKRPT VARCHAR_10 NOT NULL,
ZSDSKRPT VARCHAR_10 NOT NULL,
ASLANG VARCHAR_50,
ZSLANG VARCHAR_50,
ID INTEGER NOT NULL
);
CREATE TABLE LIT (
ID INTEGER NOT NULL,
VF VARCHAR_50,
HRSG VARCHAR_50,
ORT VARCHAR_20,
VERLAG VARCHAR_20,
BANDHEFT VARCHAR_3,
JAHR VARCHAR_4,
SEITE VARCHAR_10,
PREIS VARCHAR_10,
ISBN VARCHAR_14,
STANDNR VARCHAR_20,
ANM1 VARCHAR_60,
ANM2 VARCHAR_60,
AUT INTEGER,
DATUM DATE,
TYP VARCHAR_3,
SPRACHEN VARCHAR_20,
QCODE VARCHAR_15
);
CREATE TABLE TITELAUFS_LIT (
ID INTEGER NOT NULL,
TITELAUFS VARCHAR_60 NOT NULL,
LIT_ID INTEGER NOT NULL
);
CREATE TABLE TITELWERK_LIT (
ID INTEGER NOT NULL,
TITELWERK VARCHAR_60 NOT NULL,
ID_LIT INTEGER NOT NULL
);
/******************************************************************************/
/**** Unique Constraints ****/
/******************************************************************************/
ALTER TABLE DSKRPTS ADD CONSTRAINT UNQ_DSKRPTS UNIQUE (ASDSKRPT, ZSDSKRPT);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE AUT ADD CONSTRAINT PK_AUT PRIMARY KEY (ID);
ALTER TABLE DICENTRIES ADD PRIMARY KEY (ID);
ALTER TABLE DSKRPTLINK_DICENTRIES ADD PRIMARY KEY (ID);
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT PK_DSKRPTLINK_D_LIT PRIMARY KEY (ID);
ALTER TABLE DSKRPTS ADD PRIMARY KEY (ID);
ALTER TABLE D_LIT ADD CONSTRAINT PK_D_LIT PRIMARY KEY (ID);
ALTER TABLE LIT ADD CONSTRAINT PK_LIT PRIMARY KEY (ID);
ALTER TABLE TITELAUFS_LIT ADD CONSTRAINT PK_TITELAUFS_LIT PRIMARY KEY (ID);
ALTER TABLE TITELWERK_LIT ADD CONSTRAINT PK_TITELWERK_LIT PRIMARY KEY (ID);
/******************************************************************************/
/**** Foreign Keys ****/
/******************************************************************************/
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_1 FOREIGN KEY (ASVERW) REFERENCES DICENTRIES (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_2 FOREIGN KEY (ZSVERW) REFERENCES DICENTRIES (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_3 FOREIGN KEY (ASQCODE) REFERENCES LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_4 FOREIGN KEY (ZSQCODE) REFERENCES LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_5 FOREIGN KEY (AUT) REFERENCES AUT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DICENTRIES ADD CONSTRAINT FK_DICENTRIES_6 FOREIGN KEY (UPDAUT) REFERENCES AUT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DSKRPTLINK_DICENTRIES ADD CONSTRAINT FK_DSKRPTLINK_DICENTRIES_1 FOREIGN KEY (ID_DICENTRY) REFERENCES DICENTRIES (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DSKRPTLINK_DICENTRIES ADD CONSTRAINT FK_DSKRPTLINK_DICENTRIES_2 FOREIGN KEY (ID_DSKRPT) REFERENCES DSKRPTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT FK_DSKRPTLINK_D_LIT_1 FOREIGN KEY (ID_D_LIT) REFERENCES D_LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DSKRPTLINK_D_LIT ADD CONSTRAINT FK_DSKRPTLINK_D_LIT_2 FOREIGN KEY (ID_DSKRPT) REFERENCES DSKRPTS (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE D_LIT ADD CONSTRAINT FK_D_LIT_1 FOREIGN KEY (ID_LIT) REFERENCES LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TITELAUFS_LIT ADD CONSTRAINT FK_TITELAUFS_LIT_1 FOREIGN KEY (LIT_ID) REFERENCES LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TITELWERK_LIT ADD CONSTRAINT FK_TITELWERK_LIT_1 FOREIGN KEY (ID_LIT) REFERENCES LIT (ID) ON DELETE CASCADE ON UPDATE CASCADE;
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE UNIQUE INDEX AUT_IDX1 ON AUT (AUT);
CREATE INDEX AUT_IDX2 ON AUT (FIRMA1);
CREATE INDEX AUT_IDX3 ON AUT (FIRMA2);
CREATE INDEX AUT_IDX4 ON AUT (ORT);
CREATE INDEX AUT_IDX5 ON AUT (PLZ);
CREATE INDEX AUT_IDX6 ON AUT (TYP);
CREATE INDEX AUT_IDX7 ON AUT (VF);
CREATE INDEX DICENTRIES_IDX1 ON DICENTRIES (ASABK);
CREATE INDEX DICENTRIES_IDX10 ON DICENTRIES (ZSQCODE);
CREATE INDEX DICENTRIES_IDX11 ON DICENTRIES (ZSTERM);
CREATE INDEX DICENTRIES_IDX2 ON DICENTRIES (ASTERM);
CREATE INDEX DICENTRIES_IDX3 ON DICENTRIES (DATUM);
CREATE INDEX DICENTRIES_IDX4 ON DICENTRIES (PROJ);
CREATE INDEX DICENTRIES_IDX5 ON DICENTRIES (REV);
CREATE INDEX DICENTRIES_IDX6 ON DICENTRIES (AUT);
CREATE INDEX DICENTRIES_IDX7 ON DICENTRIES (UPDAUT);
CREATE INDEX DICENTRIES_IDX8 ON DICENTRIES (UPDDATUM);
CREATE INDEX DICENTRIES_IDX9 ON DICENTRIES (ZSABK);
CREATE INDEX GEN_DSKRPTLINK_DICENTRIES_IDX1 ON DSKRPTLINK_DICENTRIES (ID_DICENTRY);
CREATE INDEX GEN_DSKRPTLINK_DICENTRIES_IDX2 ON DSKRPTLINK_DICENTRIES (ID_DSKRPT);
CREATE UNIQUE INDEX DSKRPTS_IDX1 ON DSKRPTS (ASDSKRPT);
CREATE UNIQUE INDEX DSKRPTS_IDX2 ON DSKRPTS (ZSDSKRPT);
CREATE INDEX LIT_IDX10 ON LIT (VERLAG);
CREATE INDEX LIT_IDX6 ON LIT (HRSG);
CREATE INDEX LIT_IDX7 ON LIT (QCODE);
CREATE INDEX LIT_IDX8 ON LIT (TYP);
CREATE INDEX LIT_IDX9 ON LIT (VF);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: AUT_BI0 */
CREATE TRIGGER AUT_BI0 FOR AUT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_aut_id,1);
end
^
/* Trigger: DICENTRIES_BI0 */
CREATE TRIGGER DICENTRIES_BI0 FOR DICENTRIES
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID = gen_id(gen_dicentries_id,1);
end
^
/* Trigger: DSKRPTLINK_DICENTRIES_BI0 */
CREATE TRIGGER DSKRPTLINK_DICENTRIES_BI0 FOR DSKRPTLINK_DICENTRIES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_DSKRPTLINK_DICENTRIES_ID,1);
end
^
/* Trigger: DSKRPTLINK_D_LIT_BI0 */
CREATE TRIGGER DSKRPTLINK_D_LIT_BI0 FOR DSKRPTLINK_D_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_DSKRPTLINK_D_LIT_ID,1);
end
^
/* Trigger: DSKRPTS_BI0 */
CREATE TRIGGER DSKRPTS_BI0 FOR DSKRPTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID = gen_id(gen_dskrpts_id,1);
end
^
/* Trigger: D_LIT_BI0 */
CREATE TRIGGER D_LIT_BI0 FOR D_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(GEN_D_LIT_ID,1);
end
^
/* Trigger: LIT_BI0 */
CREATE TRIGGER LIT_BI0 FOR LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_lit_id,1);
end
^
/* Trigger: TITELAUFS_LIT_BI0 */
CREATE TRIGGER TITELAUFS_LIT_BI0 FOR TITELAUFS_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_titelaufs_lit_id,1);
end
^
/* Trigger: TITELWERK_LIT_BI0 */
CREATE TRIGGER TITELWERK_LIT_BI0 FOR TITELWERK_LIT
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.ID = gen_id(gen_titelwerk_lit_id,1);
end
^
SET TERM ; ^
Um Rekursion zu verstehen, muss man zunächst Rekursion verstehen.
|