base de datos

CREATE TABLE persona
(
  per_ide serial NOT NULL,
  per_pat character varying(200) NOT NULL, -- apellido paterno
  per_mat character varying(200) NOT NULL, -- apellido materno
  per_nom character varying(200) NOT NULL, -- nombre
  fec_nac date NOT NULL DEFAULT ('now'::text)::date, -- nacionalidad
  pai_ide integer NOT NULL, -- ide del país (FK)
  per_com character varying(200) DEFAULT ''::character varying, -- nombre completo
  per_sex character varying(1) NOT NULL DEFAULT ''::character varying,
  per_doc character varying(20) DEFAULT ''::character varying,
  per_ema character varying(250) DEFAULT ''::character varying,
  tip_doc integer DEFAULT 1,
  per_dir character varying(300) DEFAULT ''::character varying,
  cod_mun integer,
  CONSTRAINT persona_pkey PRIMARY KEY (per_ide),
  CONSTRAINT persona_pai_ide_fkey FOREIGN KEY (pai_ide)
      REFERENCES sogem.pais (pai_ide) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT persona_per_ide_key UNIQUE (per_ide)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sogem.persona
  OWNER TO postgres;
COMMENT ON COLUMN sogem.persona.per_pat IS 'apellido paterno';
COMMENT ON COLUMN sogem.persona.per_mat IS 'apellido materno';
COMMENT ON COLUMN sogem.persona.per_nom IS 'nombre ';
COMMENT ON COLUMN sogem.persona.fec_nac IS 'nacionalidad';
COMMENT ON COLUMN sogem.persona.pai_ide IS 'ide del país (FK)';
COMMENT ON COLUMN sogem.persona.per_com IS 'nombre completo';


-- Index: sogem."IX_Relationship78_persona"

-- DROP INDEX sogem."IX_Relationship78_persona";

CREATE INDEX "IX_Relationship78_persona"
  ON sogem.persona
  USING btree
  (pai_ide);

-- Index: sogem.idx_personas_repetidas

-- DROP INDEX sogem.idx_personas_repetidas;

CREATE UNIQUE INDEX idx_personas_repetidas
  ON sogem.persona
  USING btree
  (per_doc COLLATE pg_catalog."default")
  WHERE length(per_doc::text) = 8;


-- Trigger: tr_persona on sogem.persona

-- DROP TRIGGER tr_persona ON sogem.persona;

CREATE TRIGGER tr_persona
  BEFORE INSERT OR UPDATE
  ON sogem.persona
  FOR EACH ROW
  EXECUTE PROCEDURE sogem.fn_tr_persona();