Sistema AIOCAP

--Aggiungere i numeratori per bnet, bnrami, bnnodi, bncpt
CREATE TABLE bnet (
    codice INTEGER NOT NULL,
    descrizio CHARACTER VARYING(201),
    codubi CHARACTER VARYING(51)
);
ALTER TABLE ONLY bnet
    ADD CONSTRAINT ix_bnet_codice PRIMARY KEY (codice);
ALTER TABLE ONLY bnet
    ADD CONSTRAINT ix_bnet_codubi FOREIGN KEY (codubi) REFERENCES ubicaz(codice) ON UPDATE CASCADE ON DELETE CASCADE;
 
CREATE TABLE bnnodi (
    codprg INTEGER,
    codice INTEGER NOT NULL,
    descrizio CHARACTER VARYING(201),
    id INTEGER,
    codcau CHARACTER VARYING(51),
    codocap INTEGER,
    tipo VARCHAR(2),
    t INTEGER
);
ALTER TABLE ONLY bnnodi
    ADD CONSTRAINT ix_bnnodi_codice PRIMARY KEY (codice);
ALTER TABLE ONLY bnnodi
    ADD CONSTRAINT ix_bnnodi_codcau FOREIGN KEY (codcau) REFERENCES causanc(codice) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY bnnodi
    ADD CONSTRAINT ix_bnnodi_codprg FOREIGN KEY (codprg) REFERENCES bnet(codice) ON UPDATE CASCADE ON DELETE CASCADE;
 
CREATE TABLE bnrami (
    codprg INTEGER,
    codice INTEGER NOT NULL,
    id INTEGER,
    nodosx INTEGER,
    nododx INTEGER
);
ALTER TABLE ONLY bnrami
    ADD CONSTRAINT ix_bnrami_codice PRIMARY KEY (codice);
ALTER TABLE ONLY bnrami
    ADD CONSTRAINT ix_bnrami_codprg FOREIGN KEY (codprg) REFERENCES bnet(codice) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY bnrami
    ADD CONSTRAINT ix_bnrami_nododx FOREIGN KEY (nododx) REFERENCES bnnodi(codice) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY bnrami
    ADD CONSTRAINT ix_bnrami_nodosx FOREIGN KEY (nodosx) REFERENCES bnnodi(codice) ON UPDATE CASCADE ON DELETE CASCADE;
 
CREATE TABLE bncpt (
    codice INTEGER NOT NULL,
    codnodo INTEGER,
    val CHARACTER VARYING(2048),
    p DOUBLE PRECISION
);
ALTER TABLE ONLY bncpt
    ADD CONSTRAINT ix_cpt_codice PRIMARY KEY (codice);
ALTER TABLE ONLY bncpt
    ADD CONSTRAINT ix_cpt_codnodo FOREIGN KEY (codnodo) REFERENCES bnnodi(codice) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY bnrami
    ADD CONSTRAINT ix_bnrami_sx_dx UNIQUE (nodosx, nododx);