address_objects.sql

-- wrap in transaction
BEGIN;

-- create new customers.address table
CREATE SEQUENCE IF NOT EXISTS customers.address_address_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE customers.address_address_id_seq
    OWNER TO pagila_admin;

CREATE TABLE IF NOT EXISTS customers.address (
    address_id integer DEFAULT nextval('address_address_id_seq'::regclass) NOT NULL PRIMARY KEY,
    address text NOT NULL,
    address2 text,
    district text NOT NULL,
    city_id smallint NOT NULL REFERENCES common.city ON UPDATE CASCADE ON DELETE RESTRICT,
    postal_code text,
    phone text NOT NULL,
    last_update timestamp with time zone DEFAULT now() NOT NULL
);

ALTER TABLE customers.address
    OWNER TO pagila_admin;

CREATE INDEX IF NOT EXISTS idx_fk_city_id ON customers.address(city_id);

CREATE TRIGGER last_updated
    BEFORE UPDATE ON customers.address FOR EACH ROW
    EXECUTE PROCEDURE last_updated();

COMMIT;