Integridad de Datos

En este capítulo se explica cómo las restricciones de integridad aplican las reglas de negocio asociadas a una base de datos y evitan la introducción de información no válida en las tablas.

Introducción a Integridad de Datos

Es importante que los datos mantengan la integridad de datos, que es el cumplimiento de las reglas de negocio determinadas por el administrador de la base de datos o el desarrollador de aplicaciones.

Las reglas de negocio especifican condiciones y relaciones que siempre deben ser verdaderas o siempre deben ser falsas. Por ejemplo, cada compañía define sus propias políticas sobre salarios, números de empleados, seguimiento de inventario, etc.

Técnicas para garantizar la integridad de los datos

Al diseñar una aplicación de base de datos, los desarrolladores tienen varias opciones para garantizar la integridad de los datos almacenados en la base de datos.

Estas opciones incluyen:

Consulte además:

Ventajas de las Restricciones de Integridad

Una restricción de integridad es un objeto de esquema que se crea y borra mediante SQL. Para aplicar la integridad de los datos, utilice restricciones o afirmaciones de integridad siempre que sea posible.

Las ventajas de las restricciones y afirmaciones de integridad sobre las alternativas para aplicar la integridad de los datos incluyen:

Consulte además:

Tipos de Restricciones de Integridad

Oracle AI Database permite aplicar restricciones tanto a nivel de tabla como de columna, así como afirmaciones que implican varias tablas.

Una restricción especificada como parte de la definición de una columna o atributo es una especificación en línea. Una restricción especificada como parte de la definición de tabla es una especificación fuera de línea.

Una clave es la columna o el juego de columnas incluidas en la definición de determinados tipos de restricciones de integridad. Las claves describen las relaciones entre las tablas y las columnas de una base datos relacional. Los valores individuales de una clave se denominan valores de clave.

En el siguiente cuadro se describen los tipos de restricciones. Cada uno se puede especificar en línea o fuera de línea, excepto NOT NULL, que debe estar en línea.

Tabla 1 Tipos de restricciones de integridad

Tipo de Restricción Descripción Consulte también
NOT NULL Permite o no permite inserciones o actualizaciones de filas que contienen un valor null en una columna especificada. "NOT NULL Integrity Constraints"
Clave Única prohíbe que varias filas tengan el mismo valor en la misma columna o combinación de columnas, pero permite que algunos valores sean nulos. "Restricciones Únicas"
Clave primaria Combina una restricción NOT NULL y una restricción única. prohíbe que varias filas tengan el mismo valor en la misma columna o combinación de columnas y prohíbe que los valores sean nulos. "Primary Key Constraints" (Restricciones de Clave Primaria)
Clave Ajena Designa una columna como clave ajena y establece una relación entre la clave ajena y una clave primaria o única, denominada clave de referencia. "Foreign Key Constraints" (Restricciones de Clave Ajena)
Cheque Requiere que un valor de base de datos cumpla una condición especificada. "Check Constraints" (Comprobar restricciones)
REF Indica los tipos de manipulación de datos permitidos en los valores de una columna REF y cómo estas acciones afectan a los valores dependientes. En una base de datos objeto-relacional, un tipo de dato incorporado denominado REF encapsula una referencia a un objeto de fila de un tipo de objeto especificado. Las restricciones de integridad referencial en las columnas REF garantizan que haya un objeto de fila para REF. Oracle AI Database Object-Relational Developer's Guide para obtener más información sobre las restricciones REF
Assertions Las afirmaciones son expresiones booleanas que implican subconsultas en las tablas de base de datos de una aplicación. Las afirmaciones tienen la semántica de una restricción, es decir, la base de datos tiene la tarea de garantizar la verdad de estas expresiones booleanas, mientras que las transacciones cambian los datos y confirman estos cambios. Afirmaciones SQL para obtener más información sobre las afirmaciones

Consulte además:

Restricciones de Integridad NOT NULL

Una restricción NOT NULL necesita que una columna de una tabla no contenga valores nulos. Un valor nulo es la ausencia de un valor. Por defecto, todas las columnas de una tabla permiten valores nulos.

Las restricciones NOT NULL están pensadas para columnas que no deben carecer de valores. Por ejemplo, la tabla hr.employees necesita un valor en la columna email. Si se intenta insertar una fila de empleado sin una dirección de correo electrónico se genera un error:

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

Solo puede agregar una columna con una restricción NOT NULL si la tabla no contiene ninguna fila o si especifica un valor por defecto.

Consulte además:

Restricciones de Clave Única

Una restricción de clave única requiere que todos los valores de una columna o un juego de columnas sean únicos. Ninguna fila de una tabla puede tener valores duplicados en una sola columna (la clave única) o un juego de columnas (la clave única compuesta) con una restricción de clave única.

Nota: El término clave sólo hace referencia a las columnas definidas en la restricción de integridad. Debido a que la base de datos aplica una restricción única mediante la creación o reutilización implícitas de un índice en las columnas de clave, el término clave única a veces se utiliza incorrectamente como sinónimo de restricción de clave única o índice único.

Las restricciones de clave única son adecuadas para cualquier columna en la que no se permitan valores duplicados. Las restricciones únicas difieren de las restricciones de clave primaria, cuya finalidad es identificar cada fila de tabla de forma única y suelen contener valores que no tienen otra importancia que ser únicos. Entre los ejemplos de claves únicas se incluyen:

Como se muestra en el ejemplo 4-1, existe una restricción de clave única en la columna email de la tabla hr.employees. La parte pertinente de la declaración es la siguiente:

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

La restricción emp_email_uk garantiza que no haya dos empleados que tengan la misma dirección de correo electrónico, como se muestra en el siguiente ejemplo:

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

A menos que también se defina una restricción NOT NULL, un valor nulo siempre cumple una restricción de clave única. Por lo tanto, las columnas con restricciones de clave únicas y restricciones NOT NULL son típicas. Esta combinación obliga al usuario a introducir valores en la clave única y elimina la posibilidad de que los datos de fila nuevos entren en conflicto con los datos de fila existentes.

Nota: Debido al mecanismo de búsqueda de restricciones de clave única en varias columnas, no puede tener valores idénticos en las columnas no nulas de una restricción de clave única compuesta parcialmente nula.

Ejemplo 7-1 Restricción única

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

Consulte además:

Restricciones de Clave Primaria

En una restricción de clave primaria, los valores del grupo de una o más columnas sujetas a la restricción identifican de forma única la fila. Cada tabla puede tener una clave primaria, que en efecto nombra la fila y garantiza que no existan filas duplicadas.

Una clave primaria puede ser natural o sustituta. Una clave natural es un identificador significativo hecho de atributos existentes en una tabla. Por ejemplo, una clave natural podría ser un código postal en una tabla de consulta.

Por el contrario, una clave sustituta es un identificador de incremento generado por el sistema que garantiza la unicidad dentro de una tabla. Normalmente, una secuencia o un UUID generan claves de sustitución.

La implantación de Oracle AI Database de la restricción de clave primaria garantiza que las siguientes afirmaciones son verdaderas:

Una situación típica que llama a una clave primaria es el identificador numérico de un empleado. Cada empleado debe tener un ID único. Un empleado se debe describir con una sola fila en la tabla employees.

En el ejemplo de Unique Constraints, se indica que un empleado existente tiene el ID de empleado de 202, donde el ID de empleado es la clave primaria. En el siguiente ejemplo se muestra un intento de agregar un empleado con el mismo ID de empleado y un empleado sin ID:

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

La base de datos aplica restricciones de clave primaria con un índice. Normalmente, una restricción de clave primaria creada para una columna crea implícitamente un índice único y una restricción NOT NULL. Tenga en cuenta las siguientes excepciones a esta regla:

Por defecto, el nombre del índice creado implícitamente es el nombre de la limitación de clave primaria. También puede especificar un nombre definido por el usuario para un índice. Puede especificar opciones de almacenamiento para el índice mediante la inclusión de la cláusula ENABLE en la sentencia CREATE TABLE o ALTER TABLE utilizada para crear la restricción.

Consulte también: Oracle AI Database Get Started with Oracle AI Database Development y Oracle AI Database Development Guide para aprender a agregar restricciones de clave primaria a una tabla

Restricciones de Clave Ajena

Cada vez que dos tablas contienen una o más columnas comunes, Oracle AI Database puede aplicar la relación entre las dos tablas mediante una restricción de clave ajena, también denominada restricción de integridad referencial.

Una restricción de clave ajena necesita que para cada valor de la columna en la que se define la restricción coincida el valor de la otra tabla y columna especificadas. Un ejemplo de regla de integridad referencial es que un empleado sólo puede trabajar para un departamento existente.

En la siguiente tabla, se muestran los términos asociados con las restricciones de integridad referencial.

Tabla 2 Términos de Restricción de Integridad Referencial

Término Definición
Clave Ajena

Columna o juego de columnas incluidas en la definición de la restricción que hacen referencia a una clave a la que se hace referencia. Por ejemplo, la columna department_id en employees es una clave ajena que hace referencia a la columna department_id en departments.

Las claves ajenas se pueden definir como varias columnas. Sin embargo, una clave ajena compuesta debe hacer referencia a una clave primaria o única compuesta con el mismo número de columnas y los mismos tipos de dato.

El valor de las claves ajenas puede coincidir con el valor de clave primaria o única al que se hace referencia, o bien ser nulo. Si alguna columna de una clave ajena compuesta es nula, las partes no nulas de la clave no tienen que coincidir con ninguna parte correspondiente de una clave principal.
Clave de referencia Clave única o clave primaria de la tabla a la que hace referencia una clave ajena. Por ejemplo, la columna department_id de departments es la clave a la que se hace referencia para la columna department_id de employees.
Tabla dependiente o secundaria Tabla que incluye la clave ajena. Esta tabla depende de los valores presentes en la clave única o primaria a la que se hace referencia. Por ejemplo, la tabla employees es un elemento secundario de departments.
Tabla principal o de referencia Tabla a la que hace referencia la clave ajena de la tabla secundaria. Es la clave a la que se hace referencia en esta tabla la que determina si se permiten inserciones o actualizaciones específicas en la tabla secundaria. Por ejemplo, la tabla departments es un elemento principal de employees.

La Figura 7-1 muestra una clave ajena en la columna employees.department_id. Garantiza que todos los valores de esta columna deben coincidir con un valor de la columna departments.department_id. Por lo tanto, no puede haber números de departamento erróneos en la columna employees.department_id.

Figura 1: Restricciones de Integridad Referencial

Descripción de la ilustración cncpt056.gif

Restricciones de integridad autorreferencial

Una restricción de integridad autorreferencial es una clave ajena que hace referencia a una clave principal en la misma tabla.

En la siguiente figura, una restricción autorreferencial garantiza que cada valor de la columna employees.manager_id se corresponda con un valor existente de la columna employees.employee_id. Por ejemplo, el mánager del empleado 102 debe existir en la tabla employees. Esta restricción elimina la posibilidad de números de empleado erróneos en la columna manager_id.

Figura 2: Restricciones Referenciales de Tabla Única

Descripción de la ilustración cncpt057.gif

Nulos y claves ajenas

El modelo relacional permite que el valor de las claves ajenas coincida con el valor de clave primaria o única al que se hace referencia, o sea nulo. Por ejemplo, es posible que una fila de hr.employees no especifique un ID de departamento.

Si alguna columna de una clave ajena compuesta es nula, las partes no nulas de la clave no tienen que coincidir con ninguna parte correspondiente de una clave principal. Por ejemplo, una tabla reservations puede contener una clave ajena compuesta en las columnas table_id y date, pero table_id es nulo.

Modificaciones de clave principal y claves ajenas

La relación entre la clave ajena y la clave principal tiene implicaciones para la supresión de claves principales. Por ejemplo, si un usuario intenta eliminar el registro de este departamento, ¿qué sucede con los registros de los empleados de este departamento?

Cuando se modifica una clave principal, las restricciones de integridad referencial pueden especificar las siguientes acciones que se deben realizar en las filas dependientes de una tabla secundaria:

En la Tabla 7-3 se describen las sentencias DML permitidas por las diferentes acciones referenciales en los valores clave de la tabla principal y los valores de clave ajena de la tabla secundaria.

Tabla 3 Sentencias DML permitidas por actualización y supresión sin acción

Sentencia DML Emitido contra tabla principal Emitido contra tabla secundaria
INSERT Siempre correcto si el valor de la clave principal es único Aceptar solo si el valor de clave ajena existe en la clave principal o es parcial o nulo
UPDATE NO ACTION Se permite si la sentencia no deja ninguna fila en la tabla secundaria sin un valor de clave principal al que se hace referencia Se permite si el nuevo valor de clave ajena sigue haciendo referencia a un valor de clave al que se hace referencia
DELETE NO ACTION Se permite si ninguna fila de la tabla secundaria hace referencia al valor de clave principal Siempre correcto
DELETE CASCADE Siempre correcto Siempre correcto
DELETE SET NULL Siempre correcto Siempre correcto

Nota: Otras acciones referenciales no soportadas por las restricciones de integridad FOREIGN KEY de Oracle Database se pueden aplicar mediante disparadores de base de datos. Consulte "Visión General de Disparadores".

Consulte también: Referencia de lenguaje SQL de Oracle AI Database para obtener más información sobre la cláusula ON DELETE

Índices y Claves Ajenas

Como regla general, las claves ajenas se deben indexar. La única excepción es cuando la clave única o primaria coincidente nunca se actualiza ni se suprime.

La indexación de claves ajenas en tablas secundarias proporciona las siguientes ventajas:

Consulte además:

Restricciones de Control

Una restricción de control en una columna o un juego de columnas requiere que una condición especificada sea verdadera o desconocida para cada fila.

Si DML da como resultado la condición de la restricción que se evalúa como false, se realiza un rollback de la sentencia SQL. El principal beneficio de las restricciones de control es la capacidad de aplicar reglas de integridad muy específicas. Por ejemplo, puede utilizar restricciones de control para aplicar las siguientes reglas en la tabla hr.employees:

En el siguiente ejemplo se crea una restricción de salario máximo en employees y se muestra lo que sucede cuando una sentencia intenta insertar una fila que contiene un salario que supera el máximo:

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

Una sola columna puede tener varias restricciones de control que hagan referencia a la columna en su definición. Por ejemplo, la columna salary podría tener una restricción que impida valores superiores a 10000 y una restricción independiente que impida valores inferiores a 500.

Si existen varias restricciones de control para una columna, se deben diseñar para que sus objetivos no entren en conflicto. No se puede asumir ningún orden de evaluación de las condiciones. La base de datos no verifica que las condiciones de comprobación no se excluyan mutuamente.

Consulte también: Referencia de lenguaje SQL de Oracle AI Database para obtener información sobre las restricciones para las restricciones de control

Restricciones CHECK Comprobables Previamente

Una restricción de control marcada como PRECHECK se puede comprobar fuera de la base de datos.

La definición de una restricción de control en el estado PRECHECK se realiza al crear o modificar la tabla. El estado PRECHECK complementa los estados ENABLE y VALIDATE de una restricción y una restricción en la que puede tener los tres estados activos al mismo tiempo.

Si utiliza JSON para expresar la restricción de control, Oracle AI Database le permite exportar un esquema JSON y validar datos JSON en un validador de esquema JSON dentro del cliente de aplicación. Cuando una restricción se define en el estado PRECHECK, indica que la restricción tiene un esquema JSON equivalente que conserva la semántica de la restricción. Los desarrolladores de la aplicación cliente pueden validar previamente la restricción en la aplicación cliente. La base de datos comprueba la restricción y se genera un error si la restricción no se puede expresar en el esquema JSON.

Temas relacionados

Afirmaciones

Las afirmaciones son expresiones booleanas que implican subconsultas en las tablas de base de datos de una aplicación.

Las afirmaciones tienen la semántica de una restricción, es decir, la base de datos tiene la tarea de garantizar la verdad de estas expresiones booleanas, mientras que las transacciones cambian los datos y confirman estos cambios.

La siguiente sentencia SQL crea una afirmación, denominada NO_TRAINERS_IN_BOSTON, para exigir que no haya formadores empleados en los departamentos con sede en Boston. El nivel superior NOT EXISTS representa una expresión booleana que exige que el juego de resultados de la unión entre las tablas EMP y DEPT permanezca vacío.

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

Las afirmaciones son objetos de nivel de esquema. Esto implica que si la expresión booleana hace referencia a tablas de otros esquemas, se necesita el privilegio de objeto ASSERTION REFERENCES para acceder a dichas tablas. Esto no es diferente de una clave ajena que abarca dos esquemas, es decir, un privilegio de objeto REFERENCES debe estar en su lugar para crear correctamente una clave ajena que "apunta" a una clave primaria de una tabla en un esquema diferente.

Las afirmaciones son restricciones de integridad de datos genéricas. Se podrían utilizar para sustituir las restricciones CHECK, PRIMARY KEY, UNIQUE y FOREIGN KEY.

Por ejemplo, supongamos las siguientes restricciones en la tabla EMP:

Estas restricciones se podrían implementar con las siguientes afirmaciones:

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

Sin embargo, fuertemente le aconsejamos que utilice siempre restricciones siempre que sea posible y que solo utilice afirmaciones cuando no pueda utilizar una restricción.

Temas relacionados

Estados de Restricciones y Afirmaciones de Integridad

Como parte de la definición de restricción o afirmación, puede especificar cómo y cuándo debe aplicar Oracle AI Database la restricción o la afirmación, determinando así el estado de restricción o afirmación.

Comprueba los datos modificados y existentes

La base de datos permite especificar si una restricción o afirmación se aplica a datos existentes o futuros. Si se activa una restricción o afirmación, la base de datos comprueba los datos nuevos a medida que se introducen o actualizan. Los datos que no se ajustan a la restricción o afirmación no pueden introducir la base de datos.

Por ejemplo, la activación de una restricción NOT NULL en employees.department_id garantiza que cada fila futura tenga un ID de departamento. Si se desactiva una restricción, la tabla puede contener filas que violen la restricción.

Puede definir restricciones y afirmaciones en cualquiera de los siguientes modos de validación:

El comportamiento de VALIDATE y NOVALIDATE siempre depende de si la restricción o afirmación está activada o desactivada. En la siguiente tabla, se resumen las relaciones.

Tabla 4 Comprobaciones de datos modificados y existentes

Datos modificados Datos existentes Resumen
ENABLE VALIDATE Los datos existentes y futuros deben obedecer a la restricción o afirmación. Si se intenta aplicar una nueva restricción o afirmación a una tabla rellena, se produce un error si las filas existentes violan la restricción o afirmación.
ENABLE NOVALIDATE La base de datos comprueba la restricción o afirmación, pero no es necesario que sea verdadera para todas las filas. Por lo tanto, las filas existentes pueden violar la restricción o afirmación, pero las filas nuevas o modificadas deben cumplir las reglas. Este modo se utiliza a menudo en almacenes de datos que contienen datos existentes cuya integridad ya se ha verificado.
DISABLE VALIDATE La base de datos desactiva la restricción, borra su índice y evita la modificación de las columnas restringidas. Una afirmación no puede estar en este estado.
DISABLE NOVALIDATE La restricción o afirmación no se comprueba y no es necesariamente verdadera.

Consulte también: Referencia de lenguaje SQL de Oracle AI Database para obtener más información sobre los estados de restricción

Cuando la Base de Datos Comprueba la Validez de las Restricciones y Afirmaciones

Cada restricción y afirmación se encuentra en un estado no diferible (por defecto) o diferible. Este estado determina cuándo Oracle AI Database comprueba la validez de la restricción o afirmación.

En el siguiente gráfico se muestran las opciones para las restricciones y afirmaciones diferibles.

Figura 3: Opciones para Restricciones y Afirmaciones Diferibles

Descripción de la ilustración cncpt313.gif

Restricciones y Afirmaciones No Diferibles

En una restricción no diferible, Oracle AI Database nunca desafía la comprobación de validez de la restricción o afirmación hasta el final de la transacción. En su lugar, la base de datos comprueba la restricción o afirmación al final de cada sentencia. Si se viola la restricción o la afirmación, se realiza un rollback de la sentencia.

Por ejemplo, existe una restricción NOT NULL no diferible para la columna employees.last_name. Si una sesión intenta insertar una fila sin apellidos, la base de datos realiza inmediatamente un rollback de la sentencia porque se ha violado la restricción NOT NULL. No se ha insertado ninguna fila.

Restricciones y Afirmaciones Diferibles

Una restricción o afirmación diferible permite que una transacción utilice la cláusula SET CONSTRAINT para diferir la comprobación de esta restricción o afirmación hasta que se emita una sentencia COMMIT. Si realiza cambios en la base de datos que puedan violar la restricción o la afirmación, este valor le permite desactivar la restricción o la afirmación hasta que se hayan completado todos los cambios.

Puede definir el comportamiento por defecto para cuando la base de datos compruebe la restricción o afirmación diferible. Puede especificar cualquiera de los siguientes atributos:

Supongamos que una restricción NOT NULL diferible en employees.last_name está definida en INITIALLY DEFERRED. Un usuario crea una transacción con 100 sentencias INSERT, algunas de las cuales tienen valores nulos para last_name. Cuando el usuario intenta confirmar, la base de datos realiza un rollback de las 100 sentencias. Sin embargo, si esta restricción se define en INITIALLY IMMEDIATE, la base de datos no realizará un rollback de la transacción.

Si una restricción provoca una acción, la base de datos considera esta acción como parte de la sentencia que la ha provocado, tanto si la restricción es diferida como inmediata. Por ejemplo, la supresión de una fila en departments provoca la supresión de todas las filas en employees que hacen referencia a la fila de departamento suprimida. En este caso, la supresión de employees se considera parte de la sentencia DELETE ejecutada en departments.

Consulte también: Referencia de lenguaje SQL de Oracle AI Database para obtener información sobre los atributos de restricción y sus valores por defecto

Ejemplos de comprobación de restricciones y afirmaciones

Los siguientes ejemplos ayudan a ilustrar cuándo Oracle AI Database realiza la comprobación de restricciones y afirmaciones.

Suponga lo siguiente:

Ejemplo: inserción de un valor en una columna de clave ajena cuando no existe ningún valor de clave principal

Este ejemplo se refiere a la inserción de la primera fila en la tabla employees. Actualmente no existen filas, por lo que ¿cómo se puede introducir una fila si el valor de la columna manager_id no puede hacer referencia a un valor existente en la columna employee_id?

Algunas posibilidades son:

Los valores por defecto se incluyen como parte de una sentencia INSERT antes de analizar la sentencia. Por lo tanto, los valores de columna por defecto están sujetos a toda la comprobación de restricciones de integridad.

Ejemplo: actualización de todos los valores de clave ajena y clave principal

En este ejemplo, una restricción autorreferencial hace que las entradas de la columna manager_id de employees dependan de los valores de la columna employee_id.

La empresa ha sido vendida. Debido a esta venta, todos los números de empleado deben actualizarse para que sean el valor actual más 5000 y se coordinen con los números de empleado de la nueva compañía. Como se muestra en el siguiente gráfico, algunos empleados también son gerentes:

Figura 4: Tabla de empleados antes de las actualizaciones

Descripción de la ilustración cncpt058.gif

Puesto que los números de gerente también son números de empleado, los números de gerente también deben aumentar en 5000. Puede ejecutar la siguiente sentencia SQL para actualizar los valores:

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

Aunque se define una restricción para verificar que cada valor manager_id coincide con un valor employee_id, la sentencia anterior es válida porque la base de datos comprueba efectivamente las restricciones una vez finalizada la sentencia. La Figura 7-5 muestra que la base de datos realiza las acciones de toda la sentencia SQL antes de comprobar las restricciones.

Figura 5: Comprobación de Restricciones

Descripción de la ilustración cncpt059.gif

Los ejemplos de esta sección ilustran el mecanismo de comprobación de restricciones durante las sentencias INSERT y UPDATE, pero la base de datos utiliza el mismo mecanismo para todos los tipos de sentencias DML. La base de datos utiliza el mismo mecanismo para todos los tipos de restricciones, no solo para las restricciones autorreferenciales. Nota: Las operaciones en una vista o un sinónimo están sujetas a las restricciones de integridad definidas en las tablas base.

Ejemplo: cada mánager debe tener un empleado en el mismo departamento

En este ejemplo, se crea una afirmación para garantizar que todos los mánager tengan un empleado en el mismo departamento.

En este ejemplo, debe insertar primero el empleado y, a continuación, el mánager. Al ejecutar varias inserciones de fila, como con restricciones, las afirmaciones se validan después de la ejecución de cada sentencia INSERT. Puede definir esta afirmación en DEFERRABLE para que le permita insertar primero el gestor y, a continuación, el empleado. La validación se produciría en COMMIT en lugar de en cada sentencia INSERT.

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));