Restricciones
Una restricción es una condición de obligado cumplimiento para una o más columnas de la tabla. A cada restricción se le pone un nombre, en el caso de no poner un nombre (en las que eso sea posible) entonces el propio Oracle le coloca el nombre que es un mnemotécnico con el nombre de tabla, columna y tipo de restricción.
Su sintaxis general es:
{CREATE TABLE nombreTabla
|
ALTER TABLE nombreTabla
{ADD | MODIFY}}
(campo tipo
[propiedades
] [,...]
CONSTRAINT nombreRestricción tipoRestricción
(columnas
)
[,CONSTRAINT nombrerestricción tipoRestricción
(columnas
) ...)
Las restricciones tienen un nombre, se puede hacer que sea Oracle el que les ponga nombre, pero entonces será críptico. Por eso es mejor ponerle uno mismo. Los nombres de restricción no se pueden repetir para el mismo esquema, por lo que es buena idea incluir de algún modo 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).
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) NOT NULL);
En ese caso el nombre le coloca Oracle. La otra forma (que admite nombre) es:
CREATE TABLE cliente(dni VARCHAR2(9)
CONSTRAINT dni_sinnulos NOT NULL(dni));
Valores únicos
Las restricciones de tipo UNIQUE obligan a que el contenido de uno o más campos no puedan repetir valores. Nuevamente hay dos formas de colocar esta restricción:
CREATE TABLE cliente(dni VARCHAR2(9) UNIQUE);
En ese caso el nombre de la restricción la coloca el sistema Oracle. Otra forma es:
CREATE TABLE cliente(dni VARCHAR2(9) CONSTRAINT dni_u 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. Los campos UNIQUE son las claves candidatas de la tabla (que habrán sido detectadas en la fase de diseño de la base de datos).
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 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:
CREATE TABLE cliente( dni VARCHAR2(9) PRIMARY KEY, nombre VARCHAR(50)) ;
O, poniendo un nombre a la restricción:
CREATE TABLE cliente( dni VARCHAR2(9) CONSTRAINT cliente_pk PRIMARY KEY,
nombre VARCHAR(50)) ;
Si la clave la forman más de un campo:
CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5),
CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula) ;
Clave Foránea o Secundaria
Una clave secundaria o foránea, es uno o más campos de una tabla que están relacionados con la clave principal de los campos de otra tabla. La forma de indicar una clave foránea es:
CREATE TABLE alquiler(dni VARCHAR2(9),
cod_pelicula NUMBER(5),
CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula),
CONSTRAINT dni_fk FOREIGN KEY (dni)
REFERENCES clientes(dni),
CONSTRAINT pelicula_fk FOREIGN KEY (cod_pelicula)
REFERENCES peliculas(cod));
Esta completa forma de crear la tabla alquiler incluye sus claves foráneas, el campo dni hace referencia al campo dni de la tabla clientes y el campo cod_pelicula que hace referencia al campo cod de la tabla peliculas. También hubiera bastado con indicar sólo la tabla a la que hacemos referencia, si no se indican los campos relacionados de esa tabla, se toma su clave principal (que es lo normal).
Esto forma una relación entre dichas tablas, que además obliga al cumplimiento de la integridad referencial. Esta integridad obliga a que cualquier dni incluido en la tablaalquiler tenga que estar obligatoriamente en la tabla de clientes. De no ser así el registro no será insertado en la tabla (ocurrirá un error). Otra forma de crear claves foráneas (sólo válida para claves de un solo campo) es:
CREATE TABLE alquiler(
dni VARCHAR2(9) CONSTRAINT dni_fk
REFERENCES clientes(dni),
cod_pelicula NUMBER(5) CONSTRAINT pelicula_fk
REFERENCES peliculas(cod)
CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicu ));
Esta definición de clave secundario es idéntica a la anterior, sólo que no hace falta colocar el texto FOREIGN KEY. La integridad referencial es una herramienta imprescindible de las bases de datos relacionales. Pero provoca varios problemas. Por ejemplo, si borramos un registro en la tabla principal que está relacionado con uno o varios de la secundaria ocurrirá un error, ya que de permitírsenos borrar el registro ocurrirá fallo de integridad (habrá claves secundarios refiriéndose a una clave principal que ya no existe). Por ello Oracle nos ofrece dos soluciones a añadir tras la cláusula REFERENCES:
- ON DELETE SET NULL. Coloca nulos todas las claves secundarias relacionada con la borrada
- ON DELETE CASCADE. Borra todos los registros cuya clave secundaria es igual que la clave del registro borrado.
Si no se indica esta cláusula, no se permite el borrado de registros relacionados.
El otro problema ocurre si se desea cambiar el valor de la clave principal en un registro relacionado con claves secundarias. En muchas bases de datos se implementan soluciones consistentes en añadir ON UPDATE CASCADE o ON UPDATE SET NULL. Oracle no implementa directamente estas soluciones. Por lo que hay que hacerlo de otra forma. Las soluciones son:
- Implementar un TRIGGER para que cuando se actualice el registro se actualicen las claves secundarias (el mecanismo de funcionamiento es parecido al que se muestra en el siguiente párrafo)
- Añadir un registro igual que el que se quiere cambiar en la tabla principal, pero con el nuevo valor de la clave. Mediante una instrucción UPDATE actualizar a ese valor de clave todos los registros de la tabla secundaria cuyo valor coincida con la antigua clave. Finalmente borrar el registro en la tabla principal con el valor antiguo de la clave.
La sintaxis completa para añadir claves foráneas es:
CREATE TABLE tabla(lista_de_campos
CONSTRAINT nombreRestriccion
FOREIGN KEY (listaCampos)
REFERENCES tabla(clavePrincipalRelacionada)
[ON UPDATE {SET NULL | CASCADE}]
);
Si es de un solo campo existe esta alternativa:
CREATE TABLE tabla(lista_de_campos tipos propiedades,
nombreCampoClaveSecundaria
CONSTRAINT nombreRestriccion
REFERENCES tabla(clavePrincipalRelacionada)
[ON UPDATE {SET NULL | CASCADE}]
);
Restricciones de validación
Son restricciones que dictan una condición que deben cumplir los contenidos de una columna. La expresión de la condición es cualquier expresión que devuelva verdadero o falso, pero si cumple estas premisas:
- No puede hacer referencia a números de fila
- No puede hacer referencia a objetos de SYSTEM o SYS
- No se permiten usar las funciones SYSDATE, UID, USER y USERENV
- No se permiten referencias a columnas de otras tablas (si a las de la misma tabla)
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 importe_min
CHECK (importe>0)
CONSTRAINT importe_max
CHECK (importe<8000)
);
Para poder hacer referencia a otras columnas hay que construir la restricción de forma
independiente a la columna:
CREATE TABLE ingresos(cod NUMBER(5)
PRIMARY KEY,
concepto VARCHAR2(40) NOT NULL,
importe_max NUMBER(11,2),
importe NUMBER(11,2),
CONSTRAINT importe_maximo
CHECK (importe<importe_max)
);
Añadir restricciones
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 o FOREIGN KEY. Las restricciones NOT NULL deben indicarse mediante ALTER TABLE .. MODIFY colocando NOT NULL en el campo que se modifica.
borrar restricciones
Sintaxis:
ALTER TABLE tabla
DROP PRIMARY KEY | UNIQUE(campos)
|
CONSTRAINT nombreRestricción
[CASCADE]
La opción PRIMARY KEY elimina una clave principal (también quitará el índice UNIQUE sobre las campos que formaban la clave. UNIQUE elimina índices únicos. La opción CONSTRAINT elimina la restricción indicada. La opción CASCADE hace que se eliminen en cascada las restricciones de integridad que dependen de la restricción eliminada. 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 fecha_ck
CHECK(fecha_fin>fecha_inicio),
CONSTRAINT cod_ste_fk
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 ello habría que utilizar esta instrucción:
ALTER TABLE curso
DROP PRIMARY KEY CASCADE;
Esa instrucción elimina la clave secundaria antes de eliminar la principal. También produce error 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 CASCADE CONSTRAINT elimina 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.
Desactivar restricciones.
A veces conviene temporalmente desactivar una restricción para saltarse las reglas que impone. La sintaxis es:
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ó.
Activar restricciones
Anula la desactivación. Formato:
ALTER TABLE tabla
ENABLE CONSTRAINT nombre
[CASCADE]
Sólo 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.
Cambiar de nombre a las restricciones
Para hacerlo se utiliza este comando:
ALTER TABLE table
RENAME CONSTRAINT nombreViejo
TO nombreNuevo;
Mostrar restricciones
La vista del diccionario de datos USER_CONSTRAINTS permite identificar las restricciones colocadas por el usuario (ALL_CONSTRAINTS permite mostrar las restricciones de todos los usuarios, pero sólo 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:
