[2.1] introducción al lenguaje DDL

El DDL es la parte del lenguaje SQL que realiza la función de definición de datos del SGBD. Fundamentalmente, se encarga de la creación, modificación y eliminación de los objetos de la base de datos (es decir de los metadatos). Por supuesto es el encargado de la creación de las tablas.

Los elementos, llamados objetos, de la base de datos: tablas, vistas, columnas, índices,… se almacenan en el diccionario de datos. Por otro lado, muchos Sistemas Gestores de Bases de Datos aportan elementos para organizar estos objetos (como catálogos y esquemas).

Los objetos son manipulados y creados por los usuarios. En principio solo los administradores y los usuarios propietarios pueden acceder a cada objeto, salvo que se modifiquen los privilegios del objeto para permitir el acceso a otros usuarios.

Hay que tener en cuenta que ninguna instrucción DDL puede ser anulada por una instrucción ROLLBACK (la instrucción ROLLBACK está relacionada con el uso de transacciones, que se comentarán más adelante) por lo que hay que tener mucha precaución a la hora de utilizarlas. Es decir, las instrucciones DDL generan acciones que no se pueden deshacer. Salvo que dispongamos de alguna copia de seguridad o de otros elementos de recuperación.

[2.2] creación de bases de datos

Esta es una tarea administrativa que se comentará más profundamente en otros temas. Por ahora solo se comenta de forma simple. Crear la base de datos implica indicar los archivos y ubicaciones que se utilizarán para la misma, además de otras indicaciones técnicas y administrativas que no se comentarán en este tema.

Lógicamente solo es posible crear una base de datos si se tienen privilegios de DBA (DataBase Administrator).

El comando SQL de creación de una base de datos es CREATE DATABASE. Este comando crea una base de datos con el nombre que se indique. Ejemplo:

CREATE DATABASE prueba;

En muchos sistemas eso basta para crear la bases de datos.

En el caso de Oracle necesitamos indicar muchos más parámetros.

Ejemplo:

	LOGFILE prueba.log

	MAXLOGFILES 25

	MAXINSTANCES 10

	ARCHIVELOG

	CHARACTER SET WIN1214

	NATIONAL CHARACTER SET AL32UTF8

	DATAFILE prueba1.dbf AUTOEXTEND ON MAXSIZE 500MB;

El proceso de creación de bases de datos en Oracle es muy complejo y no se verá en estos apuntes1.

[2.2.1]objetos de la base de datos

Según los estándares actuales, una base de datos es un conjunto de objetos pensados para gestionar datos. Estos objetos están contenidos en esquemas, los esquemas suelen estar asociados al perfil de un usuario en particular.

En SQL estándar, existe el concepto de catálogo, que sirve para almacenar esquemas, y estos sirven para almacenar objetos. Así el nombre completo de un objeto vendría dado por:

catálogo.esquema.objeto

Es decir, los objetos pertenecen a esquemas y estos a catálogos.

En casi todos los sistemas de bases de datos hay un catálogo por defecto, de modo que si no se indica catálogo alguno al crear objetos, estos se almacenan allí. Del mismo modo, hay esquemas por defecto.

Oracle no posee catálogos. Sin embargo sí existen esquemas. Los esquemas de Oracle están relacionados con los usuarios: cada usuario posee un esquema (con el mismo nombre que el usuario) pensado para almacenar sus objetos. Aunque podemos almacenar (si tenemos permisos para ello) objetos en otros esquemas, nuestros objetos (tablas, vistas, etc.) estarán normalmente en nuestro esquema.

[2.3] creación de tablas

[2.3.1]nombre de las tablas

Deben cumplir las siguientes reglas (se comentan las reglas de Oracle, en otros SGBD podrían cambiar):

• Deben comenzar con una letra
• No deben tener más de 30 caracteres
• Solo se permiten utilizar letras del alfabeto (inglés), números o el signo de subrayado (también los signos $ y #, pero esos se utilizan de manera especial, por lo que no son recomendados)
• No puede haber dos tablas con el mismo nombre dentro del mismo esquema (pueden coincidir los nombres si están en distintos esquemas)
• No puede coincidir con el nombre de una palabra reservada SQL (por ejemplo no se puede llamar SELECT a una tabla)
• En el caso de que el nombre tenga espacios en blanco o caracteres nacionales (permitido solo en algunas bases de datos), entonces se suele entrecomillar con comillas dobles. En el estándar SQL 99 (respetado por Oracle) se pueden utilizar comillas dobles al poner el nombre de la tabla a fin de hacerla sensible a las mayúsculas (se diferenciará entre “FACTURAS” y “Facturas”)

[2.3.2]comando CREATE TABLE

Es la orden SQL que permite crear una tabla. Por defecto será almacenada en el espacio y esquema del usuario que crea la tabla. Sintaxis:

CREATE TABLE [esquema.] nombreDeTabla (

	nombreDeLaColumna1 tipoDeDatos [DEFAULT valor] 

        [restricciones]  [, ...]

);

Ejemplo:

CREATE TABLE proveedores (nombre VARCHAR(25));

Crea una tabla con un solo campo de tipo VARCHAR.

Solo se podrá crear la tabla si el usuario posee los permisos necesarios para ello. Si la tabla pertenece a otro esquema (suponiendo que el usuario tenga permiso para grabar tablas en ese otro esquema), se antepone al nombre de la tabla , el nombre del esquema:

CREATE TABLE otroUsuario.proveedores (

  nombre VARCHAR(25)

);

Se puede indicar un valor por defecto para el atributo mediante la cláusula DEFAULT.

Ejemplo:

CREATE TABLE Proveedores (

				nombre VARCHAR(25), 

				localidad VARCHAR(30) DEFAULT ‘Palencia’

);

De este modo si añadimos un proveedor y no indicamos localidad, se tomará Palencia como localidad de dicho Proveedor.

Podemos utilizar DEFAULT y usar funciones del sistema. Por ejemplo:

CREATE TABLE Prestamos(

		id_prestamo NUMBER(8),

		fecha_prestamo DATE DEFAULT SYSDATE

);

[2.4] tipos de datos

A la hora de crear tablas, hay que indicar el tipo de datos de cada campo. Necesitamos pues conocer los distintos tipos de datos. Estos son:

[2.4.1]textos

Para almacenar texto, Oracle dispone de los siguientes tipos

• VARCHAR2 . Utilizado para textos de longitud variable. En este tipo, para cada valor, Oracle almacena primero el tamaño del texto y luego almacena el texto. De esa forma un texto corto ocupa en disco menos que un texto largo.

  Cuando se indica VARCHAR2 como tipo, se debe de indicar también un número entre paréntesis que indicará el tamaño máximo del texto.

  Para Oracle las palabras VARCHAR y VARCHAR2 son equivalentes. Pero se aconseja utilizar VARCHAR2.

  Se admiten textos que ocupen hasta 4000 bytes como máximo.

• CHAR. Para textos de longitud fija. En las columnas que usen este tipo de datos, los valores siempre ocuparán el mismo tamaño. Por ejemplo, si hemos indicado como tipo CHAR(20), todos los textos ocuparán 20 caracteres, sean del tamaño que sean.

  El tipo CHAR, como mucho puede indicar un tamaño de 2000 bytes.

• NCHAR. Uno de los problemas en todas las bases de datos tiene que ver con la codificación del texto. A veces ocurre que la forma de codificar general de la base de datos usa una tabla de códigos concreta (por ejemplo la ISO-88591-1 o la propia de los sistemas de Windows conocida como WIN-1252).

  El tipo NCHAR permite utilizar un segundo juego de caracteres para poder almacenar textos usando la configuración regional de cada país. De hecho, NCHAR usa la tabla Unicode.

  Actualmente es un tipo en desuso en cuanto Unicode (en su formato UTF-8 que Oracle llama AL32UTF8) se ha convertido en un estándar mundial. Es decir, el tipo principal habitual hoy en día en los servidores Oracle Database es AL32UTF8.

  El resto de detalles de este tipo son los mismos que los del tipo CHAR.

• NVARCHAR2. La idea es la misma que en el caso anterior, pero ahora pensando en el almacenamiento de caracteres de longitud variable.

En todos estos tipo, conviene indicar suficiente espacio para almacenar los valores. En el caso de los VARCHAR2, no se malgasta espacio por poner más espacio del deseado ya que si el texto es más pequeño que el tamaño indicado, el resto del espacio se ocupa: aunque también es conveniente ajustar ya que evita que se pueden introducir más caracteres de los necesarios en la base de datos.

A la hora de introducir valores de tipo texto, hay que tener en cuenta que los textos literales se entrecomillan en todas las instrucciones SQL. Ejemplo:

INSERT INTO Personas(cod_persona,nombre) 

VALUES (1,‘Juan Luis’);

En el ejemplo anterior Juan Luis, es el nombre de la persona que estamos añadiendo a la tabla Personas. Puesto que es un texto, se entrecomilla con comillas simples.

Podría ocurrir que el propio texto literal requiera indicar comillas simples. En Oracle eso se soluciona mediante el operador q que permite indicar otro carácter delimitador del texto distinto de la comilla simple. Por ejemplo:

O’Hara es un texto que tiene dentro un apostrofe (es decir, una comilla simple). Tras el operador q, y entre comillas simples, se indican los nuevos delimitadores del texto (en el ejemplo son los corchetes) y dentro de ellos el texto que, evidentemente, no puede contener ninguno de los nuevos delimitadores (es decir, no puede contener corchetes).

[2.4.2]números

En Oracle, el tipo NUMBER es un tipo muy versátil que permite representar todo tipo de números.

Su rango permite almacenar números de entre 10-130 y 9,99999999999 * 10128. Si intentamos añadir un número fuera de este rango, Oracle produciría un error.

3números de coma fija

Son los números más utilizados en las bases de datos. Indicando este tipo, se nos permite almacenar números decimales de forma exacta,

Su desventaja es que ocupan más que los números de coma flotante y que, además, las computadoras no saben operar con estos números de forma nativa, por lo que los cálculos con ellos son más lentos (aunque más precisos).

Los números decimales (números de coma fija) se indican mediante el formato:

NUMBER(p,s)

Donde p es la precisión máxima del número y s es la escala (número de decimales a la derecha de la coma). Por ejemplo, NUMBER (8,3) indica que se representan números de ocho cifras de precisión y tres decimales. Los decimales en Oracle se presenta con el punto y no con la coma.

Para números enteros se indica NUMBER(p) donde p es el número de dígitos. Eso es equivalente a NUMBER(p,0).

Para números de coma flotante (equivalentes a los float o double de muchos lenguajes de programación) simplemente se indica el texto NUMBER sin precisión ni escala.

1precisión y escala

La cuestión de la precisión y la escala es compleja. Para entenderla mejor, se muestran estos ejemplos:

En definitiva, la precisión debe incluir todos los dígitos del número (puede llegar hasta 38 dígitos). La escala solo indica los decimales que se respetarán del número, pero si es negativa indica ceros a la izquierda del decimal.

4enteros

Oracle también permite almacenar números enteros. En este caso se indica la palabra NUMBER seguida del número máximo de cifras que podrán almacenarse. Por ejemplo NUMBER(8) permitiría almacenar números sin decimales de hasta ocho cifras.

5números en coma flotante

Se indican simplemente con la palabra NUMBER, sin indicar número alguno después. Como se ha indicado anteriormente, los números en coma flotante permiten almacenar números decimales que se operan muy rápido por parte de las computadoras, pero que no almacenan los números con exactitud absoluta. Tienen una precisión limitada.

[2.4.3]fechas y horas

6almacenamiento de fechas y horas

Las fechas y las horas en Oracle (como en otros sistemas) se almacenan internamente en un formato especial (realmente es un formato numérico). Sin embargo, las personas representamos las fechas indicando años, meses, día del mes, hora, minutos y segundos.

Por lo tanto hay que tener en cuenta que el formato en el que se muestran las fechas por pantalla cuando consultamos datos, es una conversión para que las personas entendamos las fechas. Internamente se almacenan de otra forma.

Oracle posee cuatro tipos de datos relacionados con fechas.

7tipo DATE

El tipo DATE permite almacenar fechas sin tener en cuenta las horas, minutos y segundos. Oracle posee la función SYSDATE para obtener la fecha actual.

Las fechas no se pueden manipular directamente y se debe usar la función TO_DATE (que se detallará en temas posteriores) para pasar un texto que representa fechas a la fecha correspondiente. Ejemplo:

TO_DATE(‘3/5/2007’,’DD/MM/YYYY’)

El resultado es la fecha que representa el 3 de mayo de 2007. La expresión DD/MM/YYYY indica que estamos usando hasta dos cifras para el día, que le siguen (separadas por el símbolo /) hasta dos cifras para el mes y finalmente 4 cifras para el año.

8tipo TIMESTAMP

Es una extensión del anterior, almacena valores de día, mes y año, junto con hora, minuto y segundos, incluso con decimales para los segundos. Con lo que representa un instante concreto en el tiempo.

Es conveniente usar la función TO_DATE para representar datos TIMESTAMP:

TO_DATE(‘2/2/2004 18:34:23’,’DD/MM/YYYY HH24:MI:SS’)

Si intentamos almacenar datos que contienen (además de la fecha) horas, minutos y segundos en una columna de tipo DATE, solo se almacenará el año, mes y día.

9intervalos

Los intervalos no son fechas son duraciones de tiempo. Oracle posee dos tipos de intervalos: intervalos de tiempo grandes (años y meses) e intervalos pequeños de tiempo (desde segundos hasta días como mucho).

1tipo INTERVAL YEAR TO MONTH

Este tipo de datos almacena intervalos que abarcan, como mucho, años y meses.

Para indicar intervalos (por ejemplo para añadir o modificar datos de intervalos) se debe utilizar la palabra INTERVAL. Ejemplos de uso para este tipo de intervalos son:

/* 123 años y seis meses */

INTERVAL ‘123-6’ YEAR TO MONTH

/* 123 años */

INTERVAL ‘123’ YEAR TO MONTH

/* 6 meses */

INTERVAL ‘6’ MONTH TO MONTH

2INTERVAL DAY TO SECOND

Representa intervalos de tiempo que expresa días, horas, minutos y/o segundos. A la hora de crear tablas, podemos indicar la precisión de los días indicando un número tras la palabra DAY. Por ejemplo:

CREATE TABLE (....

   intervalo INTERVAL DAY(3) TO SECOND,...

);

Se crearía una tabla en la que una columna llamada intervalo, serviría para indicar intervalos de días, horas, minutos y segundos en el que se pueden usar hasta tres cifras para los días.

También tras los segundos se pueden indicar números:

CREATE TABLE (....

   intervalo INTERVAL DAY(3) TO SECOND(3),...

);

En este caso, se indica que en los intervalos se puede llegar hasta indicar hasta tres decimales en los segundos.

Para indicar intervalos de este tipo al añadir o modificar datos, se utiliza la misma palabra INTERVAL. Ejemplos de indicación de datos de intervalo son:

/* 4 días 10 horas 12 minutos y 7 con 352 segundos */

INTERVAL ‘4 10:12:7,352’ DAY TO SECOND(3)

/* 4 días 10 horas 12 minutos */

INTERVAL ‘4 10:12’ DAY TO MINUTE

/* 4 días 10 horas */

INTERVAL ‘4 10’ DAY TO HOUR

/* 4 días*/

INTERVAL ‘4’ DAY

/*10 horas*/

INTERVAL ‘10’ HOUR

/*25 horas*/

INTERVAL ‘253’ HOUR

/*12 minutos*/

INTERVAL ‘12’ MINUTE

/*30 segundos */

INTERVAL ‘30’ SECOND

/*8 horas y 50 minutos */

INTERVAL ‘8:50’ HOUR TO MINUTE;

/*7 minutos 6 segundos*/

INTERVAL ‘7:06’ MINUTE TO SECOND;

/*8 horas 7 minutos 6 segundos*/

INTERVAL ‘8:07:06’ HOUR TO SECOND;

[2.4.4]datos de gran tamaño

Son tipos pensados para almacenar datos de tamaño muy grande. Son datos largos y desestructurados (no se desea sobre ellos ningún tipo de ordenación o ser usado como claves o restricciones del tipo que sea). En los datos de tipo LOB (CLOB o BLOB) se admiten hasta 4GB de información en cada valor. En los BFILE hasta 8 GB.

10CLOB

Utilizado para almacenar textos.

11BLOB

Utilizado para almacenar datos binarios. Para almacenar datos binarios se requiere utilizar las librerías o interfaces especialmente dedicados a esta tarea de Oracle.

12BFILE

Sirve para almacenar datos binarios. Pero, en este caso, los datos binarios se almacenan en archivos externos a la base de datos, normalmente dentro del servidor de base de datos. Para ello se almacena la ruta a cada archivo, mediante funciones especiales (como BFILENAME, por ejemplo).

[2.4.5]dominios

En SQL estándar tenemos la posibilidad de crear dominios. La instrucción que realiza esta labor es CREATE DOMAIN. Sintaxis:

CREATE DOMAIN name [AS] data_type

[ DEFAULT expression ]

[ restricciones [ ... ] ]

Ejemplo:

CREATE DOMAIN Tdireccion AS VARCHAR(3);

Gracias a esa instrucción podemos crear la siguiente tabla:

CREATE TABLE personal(

cod_pers SMALLINT,

nombre VARCHAR(30),

  direccion Tdireccion

)

En el caso de Oracle se puede utilizar la instrucción CREATE TYPE, aunque no es sinónimo de ésta. De hecho CREATE TYPE es una instrucción objeto-relacional y permite crear tipos avanzados de datos (que no es lo mismo que un dominio).

[2.5] consultar las tablas del usuario

[2.5.1]consultar el diccionario de datos

13diccionario de datos en SQL estándar

Todas las bases de datos disponen de posibilidades para consultar el diccionario de datos. Siguiendo las reglas de Codd, la forma de consultar los metadatos debería ser la misma que utilizamos cuando consultamos los datos de una tabla.

Es decir existen tablas (en realidad vistas) que en lugar de contener datos, contienen los metadatos de la base de datos.

En el caso de SQL estándar, el diccionario de datos es accesible mediante un esquema especial llamado INFORMATION_SCHEMA. Este esquema contiene el conjunto de vistas que muestran los metadatos de la base de datos.

Así, en concreto la vista INFORMATION_SCHEMA.TABLES obtiene una vista de las tablas creadas.

Es decir, la instrucción:

Muestra una tabla con diversas columnas, entre ellas la columna TABLE_CATALOG indica el catálogo en el que está la tabla, TABLE_SCHEMA el esquema en el que está la tabla y TABLE_NAME el nombre de la tabla.

La información sobre las columnas de las tablas se consultan a través de INFORMATION_SCHEMA.COLUMNS.

14diccionario de datos de Oracle

Muchos SGBD respetan el estándar para consultar el diccionario de datos. Pero Oracle no.

Oracle utiliza diversas vistas para mostrar las tablas de la base de datos y estas vistas están accesibles desde cualquier esquema. Solo necesitamos disponer de los permisos suficientes para consultar dichas vistas.

Así la vista USER_TABLES contiene una lista de las tablas del usuario actual. Por lo tanto si deseamos consultar qué tablas posee nuestro usuario, lanzaremos la instrucción:

SELECT * FROM USER_TABLES;

Esta vista obtiene numerosas columnas, en concreto la columna
TABLES_NAME muestra el nombre de cada tabla y OWNER el propietario (o esquema) de la misma.

Otra vista interesante es ALL_TABLES, que mostrará una lista de todas las tablas de la base de datos a las que tenemos permiso de acceso, sean del usuario que sean.

Finalmente DBA_TABLES es una tabla que contiene absolutamente todas las tablas del sistema Evidentemente, esta vista solo está disponible para usuarios administradores (DBA).

Para consultar las columnas de las tablas, Oracle posee una vista llamada USER_TAB_COLUMNS que permite consultar todas las columnas de las tablas del esquema actual. Las vistas ALL_TAB_COLUMNS y DBA_TAB_COLUMNS muestran los datos de las columnas de las tablas de los otros usuarios (según lo explicado para ALL_TABLES y DBA_TABLES).

[2.5.2]comando DESCRIBE

El comando DESCRIBE, permite obtener la estructura de una tabla. Ejemplo:

DESCRIBE existencias;

Y aparecerán los campos de la tabla proveedores. Esta instrucción no es parte del SQL estándar, pero casi es considerada así ya que casi todos los SGBD la utilizan. Un ejemplo del resultado de la orden anterior (en Oracle) sería:

[2.6] borrar tablas

La orden DROP TABLE seguida del nombre de una tabla, permite eliminar la tabla en cuestión.

Al borrar una tabla:

• Desaparecen todos los datos
• Cualquier vista y sinónimo referente a la tabla seguirá existiendo, pero ya no funcionará (conviene eliminarlos)
• Las transacciones pendientes son aceptadas (COMMIT), en aquellas bases de datos que tengan la posibilidad de utilizar transacciones.
• Lógicamente, solo se pueden eliminar las tablas sobre las que tenemos permiso de borrado.

Normalmente, el borrado de una tabla es irreversible, y no hay ninguna petición de confirmación, por lo que conviene ser muy cuidadoso con esta operación.

No obstante existe en Oracle una posibilidad de recuperación mediante el comando FLASHBACK TABLE si se ha configurado la papelera de reciclaje en el sistema Oracle. Por ello el comando DROP TABLE permite usar, al final , la palabra PURGE. Ejemplo:

DROP TABLE personas PURGE;

La palabra PURGE hace que el borrado de la tabla sea irreversible porque la tabla no pasa a la papelera de reciclaje (por lo tanto hay que ser muy cuidadoso en el uso de esta opción).

[2.7] modificar tablas

[2.7.1]cambiar de nombre a una tabla

De forma estándar (SQL estándar) se hace:

ALTER TABLE nombreViejo RENAME TO nombreNuevo;

En Oracle, además de con la orden anterior, se realiza mediante la orden RENAME (que permite el cambio de nombre de cualquier objeto). Sintaxis:

RENAME nombreViejo TO nombreNuevo;

Pero por coherencia es mejor hacerlo de la primera forma (la del estándar).

[2.7.2]borrar contenido de tablas

Oracle dispone de una orden no estándar para eliminar definitivamente los datos de una tabla

es la orden TRUNCATE. A esta orden le sigue el nombre de la tabla a borrar.

TRUNCATE hace que se elimine el contenido de la tabla, pero no la estructura en sí. Incluso borra del archivo de datos el espacio ocupado por la tabla.

Aunque solo elimina datos, se trata de una instrucción DDL (luego no es revocable) y es incondicional: es decir, no admite borrar solo una parte de una tabla.

[2.7.3]añadir columnas

Sintaxis:

Permite añadir nuevas columnas a la tabla. Se deben indicar su tipo de datos y sus propiedades si es necesario (al estilo de CREATE TABLE).

Las nuevas columnas se añaden al final, no se puede indicar otra posición (hay que recordar que el orden de las columnas no importa).

Ejemplo:

Muchas bases de datos (pero no Oracle) requieren escribir la palabra COLUMN tras la palabra ADD.

[2.7.4]borrar columnas

ALTER TABLE nombreTabla DROP(columna [,columnaSiguiente,...]);

Elimina la columna indicada de manera irreversible.

No se puede eliminar una columna si es la única columna que queda en la tabla. En ese caso, (habrá que usar el comando DROP TABLE.

Ejemplo de borrado de columna:

Al igual que en el caso anterior, en SQL estándar se puede escribir el texto COLUMN tras la palabra DROP.

[2.7.5]modificar columnas

Permite cambiar el tipo de datos y propiedades de una determinada columna. Sintaxis:

ALTER TABLE nombreTabla MODIFY(columna tipo [propiedades]

	[columnaSiguiente tipo [propiedades] ...]

Los cambios que se permiten en las columnas son (en Oracle):

• Incrementar precisión o anchura de los tipos de datos
• Solo se puede reducir la anchura si la anchura máxima de un campo si esa columna posee nulos en todos los registros, o todos los valores existentes tienen un tamaño menor o igual a la nueva anchura.
• Se puede pasar de CHAR a VARCHAR2 y viceversa (si no se modifica la anchura)
• Se puede pasar de DATE a TIMESTAMP y viceversa
• Cualquier otro cambio solo es posible si la tabla está vacía
• Si, a través de este comando, modificamos el valor de la propiedad DEFAULT de una tabla, dicho cambio solo tendrá efecto en la inserción de nuevas filas.

Ejemplo:

ALTER TABLE facturas MODIFY(fecha TIMESTAMP);

En el caso de SQL estándar en lugar de MODIFY se emplea ALTER (que además opcionalmente puede ir seguida de COLUMN).

Por ejemplo:

ALTER TABLE facturas ALTER COLUMN fecha TIMESTAMP;

[2.7.6]renombrar columna

Esto permite cambiar el nombre de una columna. Sintaxis

ALTER TABLE nombreTabla 

RENAME COLUMN nombreAntiguo TO nombreNuevo

Ejemplo:

ALTER TABLE facturas RENAME COLUMN fecha TO fechaYhora;

[2.7.7]valor por defecto

A cada columna se le puede asignar un valor por defecto durante su creación mediante la propiedad DEFAULT. Se puede poner esta propiedad durante la creación o modificación de la tabla, añadiendo la palabra DEFAULT tras el tipo de datos del campo y colocando detrás el valor que se desea por defecto.

Ejemplo:

CREATE TABLE articulo (cod NUMBER(7), nombre 	VARCHAR2(25), precio NUMBER(11,2) DEFAULT 3.5);

La palabra DEFAULT se puede añadir durante la creación o la modificación de la tabla (comando ALTER TABLE). El valor indicado con DEFAULT se aplica cuando añadimos filas a una tabla dejando el valor de la columna vacío

en lugar de NULL, a la columna se le asignará el valor por defecto indicado.

[2.7.8]establecer tablas en modo solo lectura

Se trata de una opción del comando ALTER TABLE que restringe una tabla para que solo admita operaciones de lectura (como por ejemplo la instrucción SELECT) sobre la tabla. Es decir, impide la modificación y la eliminación de sus datos.

Dicho de otra forma, no admite que se realice ninguna instrucción DML (tampoco la instrucción DROP TABLE) sobre ella.

Sintaxis:

ALTER TABLE nombreTabla READ ONLY;

Para devolver la tabla a su estado normal, se usa:

ALTER TABLE nombreTabla READ WRITE;

[2.7.9]modificación SET UNUSED

Se trata de una modificación de tabla propia de Oracle mediante la cual marcamos una o más columnas de tabla con un marcador de falta de uso (UNUSED). Se pueden marcar así, columnas que estamos detectando que no se usan mucho. Ejemplo:

ALTER TABLE personas SET UNUSED (n_seguridad_social);

Podemos consultar nuestras columnas marcadas como sin uso, de esta forma:

SELECT * FROM USER_UNUSED_COL_TABS;

Finalmente podemos eliminar las columnas marcadas como sin uso, de esta forma:

ALTER TABLE personas DROP UNUSED COLUMNS;

[2.8] establecimiento de restricciones

[2.8.1]definir restricciones

Una restricción es una condición de obligado cumplimiento para una o más columnas de la tabla2.

Las restricciones se pueden realizar cuando estamos creando (CREATE) o modificando (ALTER) una tabla. En realidad hay dos maneras de poner restricciones:

• Poner una restricción de columna. En ese caso la restricción se pone seguido a la definición de la columna. Sintaxis:

  ... columna tipo [DEFAULT expresión] [CONSTRAINT nombre] tipo, ...

• Poner una restricción de tabla. En ese caso se ponen al final de la lista de columnas. La única restricción que no se puede definir de esta forma es la de tipo NOT NULL. El resto se harían siguiendo esta sintaxis:

  columna1 definición1, columna2 definición2, ..., últimaColumna últimaDefinición, [CONSTRAINT nombre] tipo(listaColumnas) [,...otras restricciones...]

La diferencia está en que en el primer caso no se especifica la lista de columnas al definir la restricción: lógico porque se entiende perfectamente que las restricciones de columna se aplicarán a la columna en la que se definen.

[2.8.2]nombre de las restricciones

Es muy buena práctica asignar un nombre a cada restricción que implementemos. De no hacerlo, será la propia base de datos la que asigne nombre a la restricción. Esto último es mala idea, ya que el nombre asignado resultará críptico (Oracle usa el formato SYSCn donde n es un número diferente para cada restricción).

Es mejor poner un nombre nosotros para que sea más fácil de recordar.

Los nombres de restricción no se pueden repetir para el mismo esquema, debemos de buscar nombres únicos. Por ello debemos de utilizar un protocolo que nos facilite obtener fácilmente el nombre de la restricción.

Una forma muy utilizada es incluir el nombre de la tabla, los campos involucrados y el tipo de restricción en el nombre de la misma. Por ejemplo
pieza_id_pk podría indicar que el campo id de la tabla pieza tiene una clave principal (PRIMARY KEY).

Desde la empresa Oracle se aconseja la siguiente regla a la hora de poner nombre a las restricciones:

• Tres letras para el nombre de la tabla
• Carácter de subrayado
• Tres letras con la columna (o columnas) afectadas por la restricción
• Carácter de subrayado
• Dos letras con la abreviatura del tipo de restricción. La abreviatura puede ser:
  • NN. NOT NULL.
  • PK. PRIMARY KEY
  • UK. UNIQUE
  • FK. FOREIGN KEY
  • CK. CHECK (validación)

Por ejemplo para hacer que la clave principal de la tabla Alumnos sea el código del alumno, el nombre de la restricción podría ser alu_cod_pk.

Lo malo es que, incluso con este método, el nombre se puede repetir y además, en tablas complejas, no es tan fácil recordar el protocolo.

Otra opción, menos compleja, es indicar las restricciones en el esquema lógico de la base de datos. Una forma muy habitual es poner el nombre de la tabla seguida del tipo de la restricción y un número que indique el número de ese tipo de restricción en la tabla. Por ejemplo si observamos este esquema

Podemos poner como nombre localidades_pk a la clave primaria de la tabla localidades y localidades_nn2 a la restricción NOT NULL sobre la columna n_provincia en esa misma tabla.

En este caso es imprescindible tener muy bien documento el esquema relacional.

[2.8.3]prohibir nulos

La restricción NOT NULL permite prohibir los nulos en una determinada tabla. Eso obliga a que la columna tenga que tener obligatoriamente un valor para que sea almacenado el registro.

Se puede colocar durante la creación (o modificación) del campo añadiendo la palabra NOT NULL tras el tipo:

CREATE TABLE cliente(
      dni VARCHAR2(9) CONSTRAINT clientes_nn1 NOT NULL

);

La restricción NOT NULL es la única que solo se puede poner seguida al nombre de la columna a la que se aplica. La razón es que NOT NULL solo se puede aplicar a una columna a la vez.

[2.8.4]valores únicos

Las restricciones de tipo UNIQUE obligan a que el contenido de una o más columnas no puedan repetir valores en distintas filas. Ejemplo:

CREATE TABLE cliente(
      dni VARCHAR2(9) CONSTRAINT clientes_nn1 UNIQUE

);

Esta forma permite poner un nombre a la restricción. Si la repetición de valores se refiere a varios campos, la forma sería:

CREATE TABLE alquiler(

  dni VARCHAR2(9), 

  cod_pelicula NUMBER(5), 

  CONSTRAINT alquiler_uk UNIQUE(dni,cod_pelicula) 

);

La coma tras la definición del campo cod_pelicula hace que la restricción sea independiente de ese campo. Eso obliga a que, tras UNIQUE se indique la lista de campos a los que se aplica la restricción.

Incluso para un solo campo se puede colocar la restricción al final de la lista en lugar de definirlo a continuación del nombre y tipo de la columna.

Sobre las columnas con restricciones UNIQUES, automáticamente Oracle crea un índice interno (lo que acelera las labores de búsqueda y ordenación sobre esas columnas).

Hay que recordar que las claves alternativas en las tablas relacionales deben llevar restricciones UNIQUE y NOT NULL.

[2.8.5]clave primaria

La clave primaria de una tabla la forman las columnas que indican a cada registro de la misma. La clave primaria hace que los campos que la forman no puedan quedar vacíos ni repetir valores. Además pasan a formar parte del índice principal de la tabla, que se usa para acceder más rápidamente a estos datos.sean NOT NULL (sin posibilidad de quedar vacíos) y que los valores de los campos sean de tipo UNIQUE (sin posibilidad de repetición).

Si la clave está formada por un solo campo basta con:

  dni VARCHAR(9) CONSTRAINT clientes_pk PRIMARY KEY,

  nombre VARCHAR(50)

);

Si la clave está formada por más de un campo:

CREATE TABLE alquileres(dni VARCHAR(9), 

  cod_pelicula NUMBER(5), 

  CONSTRAINT alquileres_pk PRIMARY KEY(dni,cod_pelicula)

);

[2.8.6]clave secundaria o foránea

Una clave secundaria o foránea se usa para indicar que uno o más campos de una tabla que están relacionados con la clave principal (o incluso con una clave candidata) de otra tabla y, por lo tanto, no podrán contener valores que no estén relacionados en la otra tabla.

Este es un ejemplo de indicación de clave foránea:

CREATE TABLE alquileres(

  dni VARCHAR2(9) 

    CONSTRAINT alquileres_fk1 REFERENCES clientes(dni),

  cod_pelicula NUMBER(5) 

    CONSTRAINT alquileres_fk2 REFERENCES peliculas(cod),

  CONSTRAINT alquileres_pk PRIMARY KEY(dni,cod_pelicula)

);

Significa esta instrucción (en cuanto a claves foráneas) que el campo dni se relaciona con la columna dni de la tabla clientes y el cod_película con la columna cod de la tabla películas.

Si el campo al que se hace referencia es la clave principal, se puede obviar el nombre del campo:

CREATE TABLE alquileres(

  dni VARCHAR2(9) 

    CONSTRAINT alquileres_fk1 REFERENCES clientes,

  cod_pelicula NUMBER(5) 

    CONSTRAINT alquileres_fk2 REFERENCES peliculas,

  CONSTRAINT alquileres_pk PRIMARY KEY(dni,cod_pelicula)

);

En este caso se entiende que los campos hacen referencia a las claves principales de las tablas. Si la relación está formada por más de una columna, el orden de los campos debe de ser el mismo: aunque, en este caso, es mejor indicar explícitamente el nombre.

De hecho, cuando una relación la forman más de una columna, se debe (como siempre ocurre en las restricciones de más de una columna) tras la lista de columnas de la tabla. Aunque cualquier restricción (sea de una sola columna o no), se puede indicar también al final. Ejemplo:

CREATE TABLE existencias(

  tipo CHAR2(9),

  modelo NUMBER(3),

  n_almacen NUMBER(1)

  cantidad NUMBER(7),

  CONSTRAINT existencias_fk1 FOREIGN KEY(tipo,modelo) 

		REFERENCES piezas,

  CONSTRAINT existencias_fk2 FOREIGN KEY(n_almacen) 

		REFERENCES almacenes,

  CONSTRAINT existencias_pk 

      PRIMARY KEY(tipo,modelo,n_almacen)

);

Si la definición de clave secundaria se pone al final, hace falta colocar el texto FOREIGN KEY para indicar en qué campos se coloca la restricción de clave foránea. En el ejemplo anterior es absolutamente necesario (al no indicar explícitamente la lista de columnas en el apartado REFERENCES) que la clave principal de la tabla piezas a la que hace referencia la clave la formen las columnas tipo y modelo y en que estén en ese orden.

Las restricciones de tipo FOREIGN KEY provocan una restricción de integridad referencial, en la que no se pueden indicar datos en las claves secundarias que no existan en las claves principales relacionadas.

Lo malo es que la integridad referencial provoca varios problemas, debidos a sus efectos secundarios.

Por ejemplo, supongamos que relacionamos el alquiler de habitaciones en una tabla de alquileres con el dni de la persona que alquila. El dni es la clave de la tabla clientes. Bien, pues no podemos borrar una persona de la tabla de clientes que tenga alquileres. Tampoco podremos modificar su dni por la misma razón.

Ante esto, disponemos de la posibilidad de aplicar políticas especiales. Estas políticas son palabras claves que se colocan tras la cláusula REFERENCES al añadir una restricción de tipo FOREIGN KEY.

Así las políticas que dictan qué hacer cuando se borran datos principales relacionados con claves secundarias son:

• ON DELETE SET NULL. Coloca nulos en todas las claves secundarias relacionadas.
• ON DELETE CASCADE. Borra todas las filas relacionadas con aquella que hemos eliminado.
• ON DELETE SET DEFAULT. Coloca en las filas relacionadas el valor por defecto de esa columna en la columna relacionada
• ON DELETE NOTHING. No hace nada.

Las mismas se pueden aplicar en el caso de modificar claves principales. Así tendremos ON UPDATE DO NOTHING, ON UPDATE CASCADE, ON UPDATE SET NULL y ON UPDATE SET DEFAULT.

Sin embargo, Oracle solo dispone de las políticas ON DELETE CASCADE y ON DELETE SET NULL. Y por defecto aplica DO NOTHING tanto para borrar como para modificar claves primarias. No posee, por tanto, ninguna acción para la modificación (ON UPDATE) de claves primarias.

Ejemplo de establecimiento de borrado en cascada y de puesta a nullo:

CREATE TABLE alquileres(

  dni VARCHAR(9), 

  cod_pelicula NUMBER(5), 

  CONSTRAINT alquileres_pk PRIMARY KEY(dni,cod_pelicula),

  CONSTRAINT alquileres_fk1 FOREIGN KEY (dni)

	REFERENCES clientes(dni) ON DELETE SET NULL, 

  CONSTRAINT alquileres_fk2 FOREIGN KEY (cod_pelicula)

	REFERENCES peliculas(cod) ON DELETE CASCADE

);

[2.8.7]restricciones de validación

Son restricciones que dictan una condición que deben cumplir los contenidos de una columna. Una misma columna puede tener múltiples CHECKS en su definición (se pondrían varios CONSTRAINT seguidos, sin comas).

Ejemplo:

CREATE TABLE ingresos(

  cod NUMBER(5) PRIMARY KEY,

  concepto VARCHAR2(40) NOT NULL,

  importe NUMBER(11,2) CONSTRAINT ingresos_ck1 

                                    CHECK (importe>0)

                       CONSTRAINT ingresos_ck2

                                    CHECK (importe<8000)       

);

En este caso las restricciones CHECK prohíbe añadir datos cuyo importe no esté entre 0 y 8000.

Aunque sería más cómodo de esta forma:

CREATE TABLE ingresos(

  cod NUMBER(5) PRIMARY KEY,

  concepto VARCHAR2(40) NOT NULL,

  importe NUMBER(11,2) CONSTRAINT ingresos_ck1 

                       CHECK (importe>0 AND importe<8000)       

);

Para poder hacer referencia más de una columna dentro de una restricción CHECK, hay que indicar (como siempre) la restricción tras la lista de columnas de la tabla.

CREATE TABLE ingresos(

  cod NUMBER(5) PRIMARY KEY,

  concepto VARCHAR2(40) NOT NULL,

  importe_max NUMBER(11,2),

  importe NUMBER(11,2), 

  CONSTRAINT ingresos_ck1 CHECK (importe<importe_max)

);

[2.8.8]añadir restricciones a una tabla

Es posible querer añadir restricciones tras haber creado la tabla. En ese caso se utiliza la siguiente sintaxis:

ALTER TABLE tabla 

    ADD [CONSTRAINT nombre] tipoDeRestricción(columnas);

tipoRestricción es el texto CHECK, PRIMARY KEY, UNIQUE o FOREIGN KEY.

Si deseamos añadir una restricción NOT NULL se realiza mediante ALTER TABLE .. MODIFY y luego indicando la restricción que queremos añadir.

[2.8.9]borrar restricciones

Sintaxis:

ALTER TABLE tabla 

	DROP {PRIMARY KEY | UNIQUE(listaColumnas) | 

		CONSTRAINT nombreRestricción} [CASCADE]

La opción PRIMARY KEY elimina una clave principal. UNIQUE elimina la restricción de unicidad realizada sobre la lista de columnas indicadas.

Más versátil, la opción CONSTRAINT elimina la restricción cuyo nombre se indica.

La opción CASCADE hace que se eliminen en cascada las restricciones de integridad que dependen de la restricción eliminada y que, de otro modo, no permitiría eliminar dicha restricción.

Es decir, no podemos eliminar una clave primaria que tiene claves secundarias relacionadas. Pero si indicamos CASCADE al eliminar la clave primaria, todas las restricciones FOREIGN KEY relacionadas, también se eliminarán.

Por ejemplo en:

CREATE TABLE curso(

  cod_curso CHAR(7) PRIMARY KEY,

  fecha_inicio DATE,

  fecha_fin DATE,

  titulo VARCHAR2(60),

  cod_siguientecurso CHAR(7),

  CONSTRAINT cursos_ck1 CHECK(fecha_fin>fecha_inicio),

  CONSTRAINT cursos_fk1 FOREIGN KEY(cod_siguientecurso)

		REFERENCES curso ON DELETE SET NULL);

Tras esa definición de tabla, esta instrucción:

ALTER TABLE curso DROP PRIMARY KEY;

Produce este error:

ORA-02273: a esta clave única/primaria hacen referencia algunas claves ajenas

Para evitar el error:

ALTER TABLE curso DROP PRIMARY KEY CASCADE;

Esa instrucción elimina la restricción de clave secundaria cursos_fk1 antes de eliminar la principal.

También produce un error similar, esta instrucción:

ALTER TABLE curso DROP(fecha_inicio);

ERROR en línea 1: 
ORA-12991: se hace referencia a la columna en una restricción de multicolumna

El error se debe a que no es posible borrar una columna que forma parte de la definición de una instrucción. La solución es utilizar el sufijo CASCADE CONSTRAINT al eliminar la columna.

Así, se eliminan las restricciones en las que la columna a borrar estaba implicada:

ALTER TABLE curso DROP(fecha_inicio) 

                             CASCADE CONSTRAINTS;

Esta instrucción elimina la restricción de tipo CHECK en la que aparecía la fecha_inicio y así se puede eliminar la columna.

[2.8.10]activación y desactivación de restricciones

17desactivar restricciones

A veces conviene temporalmente desactivar una restricción para saltarse las reglas que impone. La sintaxis es (en Oracle):

ALTER TABLE tabla DISABLE CONSTRAINT nombre [CASCADE];

La opción CASCADE hace que se desactiven también las restricciones dependientes de la que se desactivó.

18activar restricciones

Anula la desactivación:

ALTER TABLE tabla  ENABLE CONSTRAINT nombre;

Solo se permite volver a activar si los valores de la tabla cumplen la restricción que se activa. Si hubo desactivado en cascada, habrá que activar cada restricción individualmente..

[2.8.11]cambiar de nombre a las restricciones

Para hacerlo se utiliza este comando:

ALTER TABLE table RENAME CONSTRAINT 

	nombreViejo TO nombreNuevo;

[2.8.12]consultar restricciones en el diccionario de datos

En el caso de Oracle, se puede utilizar la vista del diccionario de datos USER_CONSTRAINTS.

Esta vista permite identificar las restricciones colocadas por el usuario (ALL_CONSTRAINTS permite mostrar las restricciones de todos los usuarios, pero solo está permitida a los administradores). En esa vista aparece toda la información que el diccionario de datos posee sobre las restricciones. En ella tenemos las siguientes columnas interesantes:

En el diccionario de datos hay otra vista que proporciona información sobre restricciones, se trata de USER_CONS_COLUMNS, en dicha tabla se muestra información sobre las columnas que participan en una restricción. Así si hemos definido una clave primaria formada por los campos uno y dos, en la tabla USER_CONS_COLUMNS aparecerán dos entradas, una para el primer campo del índice y otra para el segundo. Se indicará además el orden de aparición en la restricción.

Ejemplo (resultado de la instrucción SELECT * FROM USER_CONS_COLUMNS):

En los datos anteriores, resultado de un SELECT sobre la vista
USER_CONS_COLUMNS aparece una restricción de clave primaria sobre la tabla existencias. Esta clave está formada por las columnas (tipo, modelo y n_almacen) y en ese orden. Una segunda restricción llamada pieza_fk está compuesta por tipo y modelo de la tabla existencias. Finalmente la restricción pieza_pk está formada por tipo y modelo, columnas de la tabla pieza.

Para saber de qué tipo son esas restricciones, habría que acudir a la vista USER_COL_CONSTRAINTS.