domingo, 8 de mayo de 2011

Modificar estado constraint en Oracle

Diagrama sentencia ALTER TABLE

Con modificar una constraint no nos referimos a cambiarle el tipo, condición, columnas, etc. (para ello eliminarla y volver a crearla), nos referimos a desactivar, activar u obligar UNA constraint (restricción) de UNA tabla, en una base de datos Oracle.

Utilizaremos la  instrucción ALTER TABLE. Existe diferente maneras de escribirlo dependiendo el tipo de constraint (como se puede apreciar en la imagen), de un modo general como está a continuación:
  • Una constraint desactivada (disabled, not validated) no realiza la comprobación para la que fue creada.
alter table nombre_tabla
disable constraint nombre_constraint;
  • Una constraint activada (enabled, validated) podremos activarla si la información existente cumple la restricción. Una vez activada, toda la información debe cumplir la restricción.
alter table nombre_tabla
enable constraint nombre_constraint;
  • Una constraint enforce o también conocida como obligatoria (enabled, not validated) la podemos activar aunque los datos existentes no cumplan la restricción. Pero los nuevos datos o modificaciones deben cumplir con la restricción.
alter table nombre_tabla
enforce constraint nombre_constraint;
nombre_tabla es el nombre de la tabla o sinónimo (nombre alternativo de un objeto -tabla-) al cual el usuario tiene acceso. Si la tabla pertenece a un usuario distinto del nuestro, se debe especificar el nombre del propietario (propietario.nombre_tabla) además de tener el privilegio ALTER TABLE sobre ella.

Otros enlaces:
Consultar información sobre las constraints de una tabla.
—Procedimiento que se encarga  de activar/desactivar todas las constraints de todas las tablas de un usuario.
—Web de Oracle sobre ALTER TABLE

Información de las constraints de una tabla. Oracle.

Para conocer mediante una consulta SQL algunos datos de las restricciones  (constraintsutilizando vistas del diccionario de datos de Oracle conociendo solo el nombre de la tabla podemos hacer la siguiente SELECT, en la que nos indica el propietario, nombre de la tabla, nombre de la columna que tiene la restricción, nombre de la constraint, tipo de constraint, la condición que deben cumplir las constraints tipo CHECK y el estado.

Un ejemplo mostrando información de las constraints donde la tabla se llama EMP.

select
         owner,
         table_name,
         column_name,
         constraint_name,
         constraint_type,
         search_condition,
         status
from
         all_constraints
  join all_cons_columns
  using(owner,table_name,constraint_name)
where
         table_name='EMP';

Información de las constraints

Owner es el nombre del esquema en el que se encuentra la tabla. Tipo de constraint sobre tabla puede ser: PRIMARY KEY (P), FOREIGN KEY (R), UNIQUE (U), CHECK (C). El nombre de una constraint es único por esquema.



Basándonos en la SELECT anterior, podemos modificar la cláusula WHERE para filtrar por los datos que nos interesan. Recordar que la información está en mayúsculas, así que en las condiciones del WHERE al preguntar por valores, utilizar mayúsculas.


Describir las vistas, para ver qué otra información nos dan y así modificar la SELECT para obtener lo que buscamos. En ALL_CONS_COLUMNS básicamente tenemos la información de qué columnas son las afectadas por la constraint. En ALL_CONSTRAINTS tenemos información sobre su estado (estatus, validate), constraints a las que se hace referencia en caso de una foreign key (r_constraint_name,r_owner), regla de eliminación (delete_rule), índice asociado a la constraint (index_name, index_owner), etc.

Otra alternativa a las vistas ALL_CONSTRAINTS y ALL_CONS_COLUMNS son: USER_CONSTRAINTS y USER_CONS_COLUMNS, en las que la información que nos mostraran se limita a las constraints pertenecientes al usuario con el que nos hemos conectado.


–Web de Oracle sobre la vista ALL_CONSTRAINTS y sobre ALL_CONS_COLUMNS

Privilegio para hacer referencia a tabla

Descripción gráfica foreign key

Cuando queremos crear una constraint foreign key en una tabla «tabla_o», que haga referencia a una tabla «tabla_r» que no pertenece al «usuario_o» propietario de «tabla_o», es decir, la tabla «tabla_r» a la que se hace referencia se encuentra en un esquema distinto al de «tabla_o», se necesita tener el privilegio explícito, es decir, que el «usuario_o» propietario de «tabla_o» debe tener explícito el permiso para poder hacer referencia a «tabla_r», ya que no es suya.

El «usuario_r» propietario de la tabla «tabla_r» o un usuario con privilegios para realizar el otorgamiento debe dar el permiso al «usuario_o» (propietario de «tabla_o»), ejecutando:
grant references on tabla_r to usuario_o;
Y si lo queremos quitar entonces ejecutamos:
revoke references on tabla_r from usuario_o cascade constraints;
Esta última instrucción quita el privilegio al «usuario_o» y elimina las foreign key que hacían referencia a «tabla_r».