requestDB.sql

-- ------------------------     << Requestlenditdb - V1 >>     ------------------------
-- 
--                    SCRIPT DE CRIACAO (DDL)
-- 
-- Data Criacao ...........: 03/03/2021
-- Autor(es) ..............: Rogério Júnior
-- Banco de Dados .........: PostgreSQL
-- Banco de Dados(nome) ...: requestlenditdb
-- 
-- PROJETO => 01 Base de Dados
--         => 02 Tabelas
-- ------------------------------------------------------------------------------------

CREATE DATABASE requestLendItDB
    WITH
        ENCODING = UTF8
        LC_COLLATE = 'pt_BR.UTF-8'
        LC_CTYPE = 'pt_BR.UTF-8'
        TEMPLATE = template0;

\c requestlenditdb

CREATE TABLE PRODUCT_CATEGORY (
    productCategoryId SMALLSERIAL NOT NULL,
    name TEXT NOT NULL,

    CONSTRAINT PRODUCT_CATEGORY_PK PRIMARY KEY (productCategoryId)
);

CREATE TABLE REQUEST (
    requestId UUID NOT NULL,
    productName TEXT NOT NULL,
    startDate DATE NOT NULL,
    endDate DATE NOT NULL,
    finalized boolean NOT NULL DEFAULT false,
    description TEXT NOT NULL,
    requester TEXT NOT NULL,
    lender TEXT NULL,
    productCategoryId SMALLSERIAL NOT NULL,

    CONSTRAINT REQUEST_PK PRIMARY KEY (requestId),

    CONSTRAINT REQUEST_PRODUCT_CATEGORY_FK FOREIGN KEY (productCategoryId)
        REFERENCES PRODUCT_CATEGORY (productCategoryId)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT,

    CONSTRAINT VALID_REQUESTER_EMAIL CHECK (requester ~* '^[\w\-\.]+@([\w-]+\.)+[\w-]{2,4}$'),
    CONSTRAINT VALID_LENDER_EMAIL CHECK (lender ~* '^[\w\-\.]+@([\w-]+\.)+[\w-]{2,4}$'),
    CONSTRAINT VALID_LENDER_USER CHECK (requester <> lender)
);