db/create_BTB_DB_0.1.3_Sprint6_OK225_create_htbl_responsibility.sql

/* -- Table: public."htbl_responsibility"
historisierung der Verantwortlichkeiten: neue Tabelle htbl_responsibility erstellen mit zusätzlichen Spalten: Timestamp, Transaktions-ID
------------------------------------------------- */

CREATE TABLE public."htbl_responsibility"
(
    id SERIAL PRIMARY KEY,
    fk_ref_grid_territory integer NOT NULL,
    fk_ref_branch integer NOT NULL,
    transfer_date timestamp without time zone NOT NULL,
    transaction_id integer NOT NULL,
    responsible_user character varying(100) NOT NULL,
    former_responsible_user character varying(100),
    create_user character varying(100) NOT NULL,
    create_date timestamp without time zone NOT NULL,
    mod_user character varying(100),
    mod_date timestamp without time zone
);

CREATE UNIQUE INDEX htbl_responsibility_territory_branch_user_transaction_id_unique
   ON public.htbl_responsibility (fk_ref_grid_territory ASC, fk_ref_branch ASC, transaction_id ASC);

ALTER TABLE public.htbl_responsibility
  ADD CONSTRAINT fk_grid_territory FOREIGN KEY (fk_ref_grid_territory) REFERENCES public.ref_grid_territory (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE public.htbl_responsibility
  ADD CONSTRAINT fk_branch FOREIGN KEY (fk_ref_branch) REFERENCES public.ref_branch (id)
   ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE public.htbl_responsibility
   OWNER TO btbservice;