PostgreSQL: Sentencias DDL (1)

Este es el Post #6 del Topic: Guía Práctica de PostgreSQL

El Lenguaje de Definición de Datos  (Data Definition Language – DDL) , sirve para modificar la estructura de los objetos en una base de datos.

Estas sentencias básicamente son: CREATE, ALTER, DROP y TRUNCATE.

A continuación iniciaremos con la creación de una base de datos.

«…La base de datos que diseñaremos se me entregó en la universidad en un curso con SQLServer, ahora me da gusto poder realizar lo mismo pero en PostGreSQL».

--1) CREACIÓN DE BASE DE DATOS (DATABASE):

CREATE DATABASE DBAdmision TEMPLATE template0;

--2) CREACIÓN DE ESQUEMAS (SCHEMAS) :

CREATE SCHEMA Persona;
CREATE SCHEMA Admision;

--3) CREACIÓN DE SECUENCIAS (SEQUENCE):

CREATE SEQUENCE Persona.seq_idcontacto START 1; --empieza desde 1
CREATE SEQUENCE Admision.seq_idmodalidad START 1; --empieza desde 1

--4) CREACIÓN DE TABLAS (TABLES):

create table Persona.Contacto
(
IDContacto int default nextval('Persona.seq_idcontacto'),
Nombres varchar(30) not null,
Paterno varchar(30) not null,
Materno varchar(30) not null,
Genero char(1) default('0') not null,
DNI varchar(10) null,
FechaNac date null,
FechaCreacion date not null default now()
)
;;
create table Persona.Postulante
(
IDPostulante char(10) not null ,
IDContacto int not null,
IDCarrera char(3) not null,
IDPerAcad char(6) not null,
IDModalidad int not null,
Puntaje int not null default(0),
AsistioExamen char(1) not null default('0'),
Ingreso char(1) not null default('0')
)
;;
Create Table Admision.PerAcad
(
IDPerAcad char(6) not null ,
Periodo char(4),
Ano char(1)
)
;;
create table Admision.Carrera
(
IDCarrera char(3) not null,
Nombre varchar(150) not null
)
;;
create table Admision.Modalidad
(
IDModalidad int default nextval('Admision.seq_idmodalidad'),
Modalidad varchar(100) not null
)
;;

--5) CREACIÓN DE RESTRICCIONES (CHECK):

ALTER TABLE Persona.Contacto
ADD CONSTRAINT ck_Genero
CHECK (Genero in ('0','1'))
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT ck_asistioexamen
CHECK (asistioexamen in ('0','1'))
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT ck_ingreso
CHECK (ingreso in ('0','1'))
;;
ALTER TABLE Persona.Postulante
add CONSTRAINT ck_puntaje
CHECK (
(asistioexamen = '1' and puntaje >=0)
or
(asistioexamen = '0' and puntaje =0)
)
;;

--6) CREACIÓN DE RESTRICCIONES (UNIQUE):

ALTER TABLE Admision.Carrera
ADD CONSTRAINT uq_nombrecarrera
UNIQUE(Nombre)
;;
ALTER TABLE Persona.Contacto
ADD CONSTRAINT uq_ContactoDNI
UNIQUE(DNI)
;;
ALTER TABLE Admision.Modalidad
ADD CONSTRAINT uq_Modalidad
UNIQUE(Modalidad)
;;

--7) CREACIÓN DE CLAVES PRIMARIAS (PRIMARY KEYS):

ALTER TABLE Admision.PerAcad
ADD CONSTRAINT pk_IDPerAcad
PRIMARY KEY(IDPerAcad)
;;
ALTER TABLE Admision.Carrera
ADD CONSTRAINT pk_IDCarrera
PRIMARY KEY(IDCarrera)
;;
ALTER TABLE Persona.Contacto
ADD CONSTRAINT pk_IDContacto
PRIMARY KEY(IDContacto)
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT pk_IDPostulante
PRIMARY KEY(IDPostulante)
;;
ALTER TABLE Admision.Modalidad
ADD CONSTRAINT pk_IDModalidad
PRIMARY KEY(IDModalidad)
;;

--8) CREACIÓN DE CLAVES FORÁNEAS (FOREIGN KEYS):

ALTER TABLE Persona.Postulante
ADD CONSTRAINT fk_IDCarrera
FOREIGN KEY(idcarrera)
references Admision.Carrera(IDCarrera)
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT fk_IDPerAcad
FOREIGN KEY(IDPerAcad)
references Admision.PerAcad(IDPerAcad)
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT fk_IDModalidad
FOREIGN KEY(IDModalidad)
references Admision.Modalidad(IDModalidad)
;;
ALTER TABLE Persona.Postulante
ADD CONSTRAINT fk_IDContacto
FOREIGN KEY(IDContacto)
references Persona.Contacto(IDContacto)
;;

Descargar Script: PostgreSQL Sentencias DDL.sql

CONCLUSIONES:

–1) CREACIÓN DE BASE DE DATOS (DATABASE):

Con la línea CREATE DATABASE DBAdmision TEMPLATE template0; , se crea una tabla a partir de una plantilla que trae postgres llamada template0, el cual nos crea la base de datos totalmente vacía. También existe la plantilla de nombre template1 y también está limpia.

–2) CREACIÓN DE ESQUEMAS (SCHEMAS) :

Los esquemas son importantes para agrupar objetos  según especificaciones, y al mantener organizado la base de datos permite un mejor desempeño al momento de la administración.

Si no se le asigna un esquema al objeto, postgres lo asignará implicitamente  al esquema «public«. Para definir que un objeto pertenezca a un esquema se pone el nombre del esquema seguido por un ponto <.> y el nombre del objeto, que puede ser una tabla, una secuencia, etc…

–3) CREACIÓN DE SECUENCIAS (SEQUENCE):

Podemos obviar el paso 3, si definimos el tipo de datos de sequencia ‘SERIAL’, en los campos establecidos en la creación de tablas en el paso 4, deberíar las lineas en cuestión de la siguiente forma:

IDContacto int default nextval(‘Persona.seq_idcontacto’),

cambiar por:

IDContacto SERIAL,

también

IDModalidad int default nextval(‘Admision.seq_idmodalidad’),

cambiar por:

IDModalidad SERIAL,

Con estos cambios, no será necesario realizar el paso 3, pero en realidad postgres si realiza implicitamente la creación de sequencias.

–4) CREACIÓN DE TABLAS (TABLES):

Con «FechaCreacion date not null default now(),» asignaremos la fecha actual por defecto al campo FechaCreacion por medio de la función now().

–5) CREACIÓN DE RESTRICCIONES (CHECK):

Los valores dentro de CHECK , por ejemplo «CHECK (Genero in (‘0′,’1’))», son los únicos que serán permitidos insertar en el campo establecido, en este caso se le asigna esa restricción al campo Genero.

–6) CREACIÓN DE RESTRICCIONES (UNIQUE):

Las restricciones UNIQUE, por ejemplo «UNIQUE(Modalidad)» , aseguran que en los datos en la misma columna, en este caso en el campo Modalidad, no sean repetidas, ya que no puede haber registradas 2 modalidades con el mismo nombre. Otro ejemplo se da en las tablas usuario, donde no permiten asignar el mismo login o nick a a más de una persona.

–7) CREACIÓN DE CLAVES PRIMARIAS (PRIMARY KEYS):

Las claves primarias, aparte de prevenir la duplicidad de datos, y servir como nexo para relacionarse con otras tablas, tiene otro fin también importante, que es agilizar el proceso de busqueda, no por el hecho de ser primary key, sino que al momento de la creación de una clave primaria se crea implícitamente un indice (index), del cual hablaré detalladamente en otro post.

–8) CREACIÓN DE CLAVES FORÁNEAS (FOREIGN KEYS):

Las claves foráneas son campos que servirán de nexo para la relación entre 2 tablas, la clave primaria de otra tabla se relacionará con la clave foránea de ésta.

TOPIC: CREATE, TABLE, SCHEMA, SEQUENCE, ALTER, CHECK, ADD CONSTRAINT, UNIQUE, PRIMARY KEY, FOREIGN KEY, TEMPLATE