CREATE SCHEMA addressbook;

/******************** Add Table: addressbook.contact ************************/
CREATE TABLE addressbook.contact
(
  id serial PRIMARY KEY,
  name VARCHAR(40),
  name_first VARCHAR(40),
  "comment" TEXT,
  title VARCHAR(40),
  nationality VARCHAR,
  "language" VARCHAR,
  birth_date date,
  email VARCHAR(40),
  phone_private VARCHAR(40),
  phone_work VARCHAR(40),
  phone_mobile VARCHAR(40),
  fax VARCHAR(40),
  ucm_code VARCHAR(40),
  contact_type VARCHAR(20),
  medpres VARCHAR DEFAULT NULL
);

/******************** Add Table: addressbook.contact_address ************************/
CREATE TABLE addressbook.contact_address
(
    id SERIAL PRIMARY KEY,
    contact_id INTEGER NULL,
    streetname VARCHAR(50) ,
    streetnumber VARCHAR(10) ,
    zip VARCHAR(10) ,
    locality VARCHAR(50) ,
    postbox VARCHAR(50) ,
    country VARCHAR(50) ,
    type VARCHAR(20) ,
    date DATE ,
    locality_id INTEGER
);
ALTER TABLE addressbook.contact_address ADD CONSTRAINT fk_addressbook_contact_address_id
    FOREIGN KEY (contact_id) REFERENCES addressbook.contact (id) ON DELETE CASCADE;
ALTER TABLE addressbook.contact_address ADD CONSTRAINT fk_addressbook_contact_address_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;

    

CREATE INDEX idx_addressbook_contact_address_contact_id 
	ON addressbook.contact_address USING btree (contact_id);