municipio_catastral.sql

-- Database generated with pgModeler (PostgreSQL Database Modeler).
-- pgModeler version: 1.2.0-beta
-- PostgreSQL version: 15.0
-- Project Site: pgmodeler.io
-- Model Author: ---

-- Database creation must be performed outside a multi lined SQL file. 
-- These commands were put in this file only as a convenience.
-- 
-- object: municipio | type: DATABASE --
-- DROP DATABASE IF EXISTS municipio;
--CREATE DATABASE municipio;
-- ddl-end --


CREATE EXTENSION postgis WITH SCHEMA public;


-- object: public.manzana | type: TABLE --
-- DROP TABLE IF EXISTS public.manzana CASCADE;
CREATE TABLE public.manzana (
	codigo varchar(2) NOT NULL,
	geom geometry(POLYGON, 9377),
	CONSTRAINT manzana_pk PRIMARY KEY (codigo)
);
-- ddl-end --
ALTER TABLE public.manzana OWNER TO postgres;
-- ddl-end --

-- object: postgis | type: EXTENSION --
-- DROP EXTENSION IF EXISTS postgis CASCADE;

-- ddl-end --

-- object: public.terreno | type: TABLE --
-- DROP TABLE IF EXISTS public.terreno CASCADE;
CREATE TABLE public.terreno (
	codigo varchar(9) NOT NULL,
	area_m2 decimal(10,2) NOT NULL,
	fotografia varchar(1000000),
	geom geometry(MULTIPOLYGON, 9377),
	codigo_manzana varchar(2) NOT NULL,
	CONSTRAINT terreno_pk PRIMARY KEY (codigo)
);
-- ddl-end --
ALTER TABLE public.terreno OWNER TO postgres;
-- ddl-end --

-- object: manzana_fk | type: CONSTRAINT --
-- ALTER TABLE public.terreno DROP CONSTRAINT IF EXISTS manzana_fk CASCADE;
ALTER TABLE public.terreno ADD CONSTRAINT manzana_fk FOREIGN KEY (codigo_manzana)
REFERENCES public.manzana (codigo) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
-- ddl-end --

-- object: public.construccion | type: TABLE --
-- DROP TABLE IF EXISTS public.construccion CASCADE;
CREATE TABLE public.construccion (
	codigo varchar(9) NOT NULL,
	area_m2 decimal(10,2) NOT NULL,
	numero_pisos integer,
	ph boolean NOT NULL,
	geom geometry(POLYGONZ, 9377) NOT NULL,
	codigo_manzana varchar(2) NOT NULL,
	CONSTRAINT construccion_pk PRIMARY KEY (codigo)
);
-- ddl-end --
ALTER TABLE public.construccion OWNER TO postgres;
-- ddl-end --

-- object: manzana_fk | type: CONSTRAINT --
-- ALTER TABLE public.construccion DROP CONSTRAINT IF EXISTS manzana_fk CASCADE;
ALTER TABLE public.construccion ADD CONSTRAINT manzana_fk FOREIGN KEY (codigo_manzana)
REFERENCES public.manzana (codigo) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
-- ddl-end --

-- object: public.predio | type: TABLE --
-- DROP TABLE IF EXISTS public.predio CASCADE;
CREATE TABLE public.predio (
	codigo varchar(13) NOT NULL,
	area_m2 decimal(10,2) NOT NULL,
	avaluo_catastral integer NOT NULL,
	codigo_terreno varchar(9) NOT NULL,
	CONSTRAINT predio_pk PRIMARY KEY (codigo)
);
-- ddl-end --
ALTER TABLE public.predio OWNER TO postgres;
-- ddl-end --

-- object: terreno_fk | type: CONSTRAINT --
-- ALTER TABLE public.predio DROP CONSTRAINT IF EXISTS terreno_fk CASCADE;
ALTER TABLE public.predio ADD CONSTRAINT terreno_fk FOREIGN KEY (codigo_terreno)
REFERENCES public.terreno (codigo) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
-- ddl-end --

-- object: public.propietario | type: TABLE --
-- DROP TABLE IF EXISTS public.propietario CASCADE;
CREATE TABLE public.propietario (
	cc integer NOT NULL,
	nombre varchar NOT NULL,
	fecha_nacimiento date,
	CONSTRAINT propietario_pk PRIMARY KEY (cc)
);
-- ddl-end --
ALTER TABLE public.propietario OWNER TO postgres;
-- ddl-end --

-- object: public.predio_propietario | type: TABLE --
-- DROP TABLE IF EXISTS public.predio_propietario CASCADE;
CREATE TABLE public.predio_propietario (
	codigo_predio varchar(13) NOT NULL,
	cc_propietario integer NOT NULL,
	copropiedad smallint NOT NULL,
	CONSTRAINT predio_propietario_pk PRIMARY KEY (codigo_predio,cc_propietario)
);
-- ddl-end --

-- object: predio_fk | type: CONSTRAINT --
-- ALTER TABLE public.predio_propietario DROP CONSTRAINT IF EXISTS predio_fk CASCADE;
ALTER TABLE public.predio_propietario ADD CONSTRAINT predio_fk FOREIGN KEY (codigo_predio)
REFERENCES public.predio (codigo) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
-- ddl-end --

-- object: propietario_fk | type: CONSTRAINT --
-- ALTER TABLE public.predio_propietario DROP CONSTRAINT IF EXISTS propietario_fk CASCADE;
ALTER TABLE public.predio_propietario ADD CONSTRAINT propietario_fk FOREIGN KEY (cc_propietario)
REFERENCES public.propietario (cc) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE;
-- ddl-end --