Constraint de la Tabla son un medio por el cual la Base de Datos puede hacer cumplir reglas de negocio y garantizar que los datos se ajustan al modelo entidad relación determinado por el análisis que define las estructuras de datos de la aplicación. Por ejemplo, los analistas de negocio de su organización pueden haber decidido que cada cliente y cada factura debe ser únicamente identificado por su número, facturas que no pueden emitir a un cliente antes que el cliente se ha creado, y que cada factura debe tener una fecha valida y un valor mayor a cero. Esto sería implementado por medio de la creación de Constraints (Restricción) de llave primaria en la columna CUSTOMER_NUMBER de la Tabla CUSTOMERS y la columna INVOICE_NUMBER de la tabla INVOICES, un Constraints (Restricción) de llave foránea (Foreign Key) en la tabla INVOICES referenciando la tabla CUSTOMERS, un Constraint (Restricción) de NOT NULL en la columna DATE de la tabla INVOICES (el tipo de dato DATE si mismo se asegurara de que cualquier fecha son automáticamente validas-no acepta fechas invalidas) y un Constraint (Restricción) de CHECK (comprobación) en la columna AMOUNT de la tabla INVOICES.
Cuando cualquier DML es ejecutado contra la tabla con Constraints definidos, si el DML viola un Constraints entonces la declaración completa será regresada automáticamente. Recuerde que una declaración DML que afecta muchas filas podría parcialmente tener éxito antes de que llegue un problema de Constraints con una fila en particular. Si la declaración es parte de una transacción de varias declaraciones, entonces la declaración que ya tuvo éxito se mantendrán intactos, pero Uncommitted (no confirmada).
EXAMEN
Una violación de Constraint forzara automáticamente a un Roll Back de la declaración completa que llego al problema, no solo la acción individual dentro de la declaración y no la transacción completa.
TIPOS DE CONSTRAINTS
Los tipos de CONSTRAINTS soportados por la Base de Datos Oracle son:
• UNIQUE.
• NOT NULL.
• PRIMARY KEY.
• FOREIGN KEY.
• CHECK.
Los Constraints tienen nombres, es buena práctica especificar los nombres con una convención de nombres estándar, pero si estos no son explícitamente nombrados, Oracle generara nombres.
UNIQUE CONSTRAINTS.
Un Constraint UNIQUE nomina a una columna (o combinación de columnas) para que el valor debe ser diferentes por cada fila en la tabla. Si el Constraints es basado en una única columna, esto es conocido como Columna Clave (Key Column – Llave primaria). Si el Constraints es compuesto de mas que una columna (conocido como Constraints UNIQUE llave compuesta), las columnas no tienes por qué ser el mismo tipo de dato o ser abjacentes en la definición de la tabla.
Una rareza de Constraints UNIQUE es que es posible ingresar valores NULL en las Columnas llaves. Es de hecho posible tener cualquier número de filas con valores NULL en su columna clave-llave. Así que la selección de filas en una columna clave garantizara que solo uno fila es regresada-a menos que la búsqueda por NULL, en los que pueden todas las filas en la que las columnas son NULL será devuelto.
Constraints Unique se hacen cumplir por un Índice. Cuando un Constraints Unique es definido, Oracle buscará un índice en las columnas claves y si uno no existe, será creado. Entonces cada vez que se inserta una fila, Oracle buscara en el índice para ver si el valor de la columna clave ya está presente; si este ya existe, rechazara el insert. La estructura de estos índices (conocido como índices B*Tree) no incluye los valores nulos, razón por el cual muchas files con NULL son permitidos: no existen simplemente en el índice.
Mientras que el primer objetivo del Idice es hacer cumplir el Constraints (Restricción), tiene un segundo efecto: mejora el rendimiento si las columnas claves son utilizadas en la clausula WHERE de declaraciones SQL. Sin embargo, la selección WHERE Key_Column IS NULL no puede utilizar el índice (porque no incluye NULL) y por lo tanto siempre resulta en una exploración de la tabla completa.
EXAMEN
Es posible insertar muchas filas con NULL en una columna con Constraints Unique. Esto no es posible para una columna con un Constraint Primary Key.
CONSTRAINTS NOT NULL
El Constraints NOT NULL forzara valores que deben ingresarse en la columna clave. Constraints NOT NULL se definen por columna; si el requerimiento de negocio es que un grupo de columnas todos deben tener valores, usted no puede definir un Constraints NOT NULL para todo el grupo, debe definir un Constraints NOT NULL para cada columna.
Cualquier intente para insertar una fila sin especificar valores para las columnas con Constraints NOT NULL resultara en un error. Es posible pasar por alto la necesidad de especificar un valor mediante la inclusión de una clausula DEFAULT en la columna cuando se crea la tabla, como se explica en la sección previa “Creación de Tablas”.
CONSTRAINTS PRIMARY KEY
La llave principal es el medio de localización de una fila única en una tabla. El paradigma de Base de Datos Relacional incluye un requisito que cada Tabla debería tener una Llave principal: una columna (o combinación de columnas) que pueden ser utilizados para distinguir cada fila. La base de Datos Oracle se desvía del paradigma (al igual que algunas otras implementaciones RDBMS) porque permite tablas sin llaves principales.
EN EL TRABAJO
Tablas sin llave principal son posibles, pero no es una buena idea. Incluso si las reglas de negocio no requieren la capacidad para identificar cada fila, llaves principales son a menudo necesarias para trabajos de mantenimiento.
La implementación del Constraint Primary Key es hecho de la unión del Constraints Unique y el Constraints NOT NULL. La columna clave debe tener valores únicos (Unique), y no puede ser NULL. Como con Constraints Unique, un índice debe existir en las columnas Restringidas (Constrained). Si uno no existe, un índice será creado cuando el Constraints se define. Una tabla puede tener solo una llave principal. Trate de crear un segundo, y obtendrá un error. Una tabla puede, sin embargo, tener cualquier número de columnas con Constraints Unique y NOT NULL, asi que si hay varias columnas que los analistas de negocio han decidió deben ser únicos y pobladas, uno de estos puede ser designado a la Llave principal, y los otros hechos Unique y NOT NULL. Un ejemplo puede ser una tabla de Employees, donde la dirección e-mail, número de seguro social y numero de empleado deberían todos ser requeridos y únicos.
EXAMEN
Constraints Unique y Primary Key necesitan un índice. Si uno no existe, uno será creado automáticamente.
CONSTRAINTS FOREIGN KEY
Un Constraints Foreign Key es definido en la tabla hijo de una relación padre-hijo. El Constraints nomina una columna (o columnas) en la tabla hijo que corresponde a la columna llave primaria de la tabla padre. Las columnas no tienen que tener el mismo nombre, pero deben ser del mismo tipo de dato. Constraint Foreign Key define la estructura relacional de la Base de Datos: las relaciones muchos a uno que conectan la tabla, en su tercera forma normal. Si la tabla padre tiene Constraints Unique aso como (o en lugar de) un Constraints Primary Key, estas columnas pueden ser utilizadas como base de Constraints Foreign Key, incluso si aceptan valores NULL.
Así como un Constraints Unique permite valores NULL en la columna restringida, también lo hace un Constraint Foreign Key. Usted puede insertar filas en la tabla hijo con NULL en las columnas Foreign Key-incluso si no hay una fila en la tabla padre con un valor NULL. Esto crea filas huérfanas y puede causar confusión terrible. Como una regla general, todas las columnas en un Constraints Unique y todas las columnas en un Constraints Foreign Key se defininen mejor con Constraints NOT NULL, suele ser un requisito empresaria.
EXAMEN
Un Constraint Foreign Key se define en la Tabla hijo, pero un Constraint Unique o Primary Key debe ya existir en la tabla Padre.
Intente insertar una fila en la tabla hijo para el cual no hay fila coincidente en la tabla padre dará un error. Similarmente, elimine una fila en la tabla padre obtendrá un error si ya hay filas refiriéndose a esta en la tabla hijo. Hay dos técnicas para cambiar este comportamiento. Primero, el Constraints puede ser creado como ON DELETE CASCADE. Esto significa que su una fila en la tabla padre es eliminada, oracle buscara en la tabla hijo todas las filas coincidentes y eliminarlos también. Esto sucederá automáticamente. Una técnica menos drástica es crear un Constraints como ON DELETE SET NULL. En este caso, si una fila en la tabla padre es eliminada, oracle buscara en la tabla hijo todas las filas coincidentes y establecerá la columna Foreign Key a NULL. Esto significa que las filas hijas serán huérfanas pero existirán todavía. Si la columna en la tabla hijo también tiene un Constraint NOT NULL, entonces la eliminación de la tabla padre fallará.
No es posible eliminar o truncar la tabla padre en una relación Foreign Key, incluso si no hay registros en la tabla hijo. Esto todavía aplica si las clausulas ON DELETE SET NULL o ON DELETE CASCADE fueron utilizados.
Una variación en el Constraints Foreign Key es el Constraint Foreign Key auto referenciado. Esto defina una condición donde las filas padres e hijo existen en la misma tabla. Un ejemplo sería una tabla de Employees, que incluye una columna para los empleados manager. El manager es un empleados y debe existir en la tabla. Asi que si la clave principal es la columna EMPLOYEE_NUMBER, y el manager es identificado por una columna MANAGER_NUMBER, entonces el Constraint Foreign Key indicara que el valor de la columna MANAGER_NUMBER debe referirse a un NUMBER_EMPLOYEE valido. Si un empleado es su propio manager, la fila se referirá al mismo.
CONSTRAINTS CHECK
Un Constraint Check puede ser utilizado para hacer cumplir reglas simples. Tales como que el valor ingresado en una columna deber estar dentro de un rango de valores. La regla debe ser una expresión que evaluara si es TRUE o FALSE. Las reglas pueden referirse a valores absolutos ingresados como Literales, o para otras columnas en la misma fila, y pueden hacer uso de algunas funciones. Muchos Constraints Check como usted dese puede aplicarse a una columna. Pero no es posible utilizar un subquery para evaluar si un valor es permisible, o para usar funciones tales como SYSDATE.
EN EL TRABAJO.
El Constraint NOT NULL es de hecho, implementado como un Constraint Check pre configurado.
DEFINIENDO CONSTRAINTS.
Los Constraints pueden ser definidos cuando se crea una Tabla, o agregados a la tabla posteriormente. Cuando es definido un Constraints en el tiempo de creación de la tabla, el Constraint puede ser definido en la línea con la columna a la cual se referirá, o en el final de la definición de la tabla. Es más flexible utilizar la última técnica. Por ejemplo, es imposible definir un Constraint Foreign Key que referencia dos columnas o un Constraint Check que referencia a cualquier otra columna que está siendo limitada si el Constraint es definido en la línea, pero ambos son posibles si los Constraint son definidos en el final de la tabla.
Para los Constraints que requieren un índice (los Constraints Unique y Primary Key), el índice será creado con la tabla si el Constraint es definido en tiempo de creación de la tabla.
Considere estos dos declaraciones de creación de tablas (a los cuales los números de línea se han agregados).
1 create table dept(
2 deptno number(2,0) constraint dept_deptno_pk primary key
3 constraint dept_deptno_ck check (deptno between 10 and 90),
4 dname varchar2(20) constraint dept_dname_nn not null);
5 create table emp(
6 empno number(4,0) constraint emp_empno_pk primary key,
7 ename varchar2(20) constraint emp_ename_nn not null,
8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),
9 dob date,
10 hiredate date,
11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
12 on delete set null,
13 email varchar2(30) constraint emp_email_uk unique,
14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
15 constraint emp_email_ck
16 check ((instr(email,'@') > 0) and (instr(email,'.') > 0)));
Tomando estas declaraciones línea por línea:
1. La primera tabla creada es DEPT, destinado a tener una fila por cada departamento.
2. DEPTNO es numérico, dos dígitos, sin decimales. Esto es la llave principal de la tabla. El Constraints es llamado DEPT_DEPTNO_PK.
3. Un segundo Constraint aplicado a DEPTNO es un CHECK limitando a números en el rango de 10 a 90. El Constraint es llamado DEPT_DEPTNO_CK.
4. La columna DNAME es caracteres de longitud variable, con un Constraint DEPT_DNAME_NN haciendo NOT NULLABLE.
5. La segunda tabla creada es EMP, destinada a tener una fila por cada empleado.
6. EMPNO es numérico, de hasta cuatro dígitos sin decimales. Constraints EMP_EMPNO_PK marca esta como la llave principal.
7. ENAME es caracteres de longitud variables, con un Constraint EMP_ENAME_NN haciendo NOT NULLABLE.
8. MGS es Employees Manager, que debe ser el mismo un empleado, la columna se define en la misma forma que la columna de clave principal de la tabla de EMPNO. El Constraint EMP_MGR_FK define estas columna como auto referencia de Foreign Key, asi cualquier valor ingresado deber hacer referencia a una fila ya existente en EMP (aunque no está obligado a no ser nula, por lo que se puede dejar en blanco).
9. DOB, la fecha de nacimiento de empleado, es un DATE y no hay Constraint.
10. HIREDATE es la fecha de contratación del trabajador y no está restringido. Al menos, no todavía.
11. DEPTNO es el departamento con el cual el empleado está asociado. La columna es definida en la misma forma como la columna de llave principal de la tabla DEPT, y el Constraints EMP_DEPTNO_FK hace cumplir una relación FOREIGN KEY, no es posible asignar un empleado a un Departamento que no existe. Esto es NULLABKE, por lo tanto.
12. El Constraints EMP_DEPTO_FK se define como ON DELETE SET NULL, asi si el registro padre en DEPT es eliminado, todos los coincidentes registros hijos en EMPNO tendrán en DEPTNO establecido a NULL.
13. EMAIL es carácter de Longitud variable y debe ser único si ingresa.(aunque puede ser vacio).
14. Este define un nivel adicional de tabla Constraint EMP_HIREDATE_CK. El Constraints Check para el uso de labor infantil, al rechazar las filas cuando la fecha de contratación no es por lo menos dieciséis años más tarde que el cumpleaños. Esta restricción no puede ser definido de acuerdo con HIREDATE, porque la sintaxis no permite las referencias a otras columnas en ese punto.
15. Un Constraint adicional EMP_EMAIL_CK agregado a la columna EMMAIL, que hace 2 comprobaciones en la dirección EMAIL. La función INSTTR busca caracteres “@” y “.” (que siempre estará presente en una dirección válida de correo electrónico) y si no puede encontrar tanto de ellos, la condición de verificación devolverá FALSE y la fila será rechazada.
Los ejemplos anteriores muestran varis posibilidades para definir Constraints en una tabla en tiempo de creación. Otras posibilidades que no son cubiertas son:
• Controlar la creación del índice para los Constraint UNIQUE y PRIMARY KEY.
• Definir si el Constraint se debe comprobar en el momento de insertar (que es po defecto) o posteriormente, cuando la transacción es COMMITTED.
• Indicando si el Constraint de hecho se está haciendo cumplir en absoluto (que es el defecto) o es lisiado.
Es posible crear tablas sin restricciones y entonces agregar posteriormente con un comando ALTER TABLE. Al final el resultado será el mismo, pero esta técnica tiene el código de menos auto-documentado, como la definición de la tabla completa entonces, se extenderá durante varias declaraciones en lugar de uno.
ESTADO DEL CONSTRAINT
En cualquier momento, cada Constraint es activado o desactivado, y validado o no validado. Cualquier combinación de estos es sintácticamente posible.
• ENABLE VALIDATE no es posible ingresar filas que violarían el Constraint, y todas las filas en la tabla se ajustan al Constraint.
• DISABLE NOVALIDATE Cualquier dato (que conforma o no) puede ser ingresado, y ya puede datos no conformes en la tabla.
• ENABLE NOVALIDATE puede ya haber datos no conformes en la tabla, pero todos los datos ingresado ahora deben conformar.
• DISABLE VALIDATE una situación imposible: todos los datos en la tabla se ajusta a la restricción, pero las nuevas no es necesario. El resultado final es que la tabla está bloqueada contra los comandos DML.
La situación ideal (y el valor por default cuando un Constraints es definido) es ENABLE VALIDATE. Esto garantizara que todos los datos son validos, y ningún dato inválido puede ser ingresado. En el otro extremo, DISABLE NOVALIDATE, puede ser muy útil cuando carga grandes cantidades de datos en la Tabla. Es muy posible que los datos que se cargan no se ajusten a las reglas de negocio. Pero en lugar de tener una gran carga fallida debido a unas malas pocas filas, poner el Constraints es este estado permitirá que la carga de tener éxito. Inmediatamente después de la carga, la transición del Constraints en estado ENABLE NOVALIDATE. Esto evitara que la situación se deteriore aun mas mientras que los datos se comprueba antes de la transición del Constraint a el estado ideal.
Como ejemplo, considere este script, cual lee datos desde una tabla fuente de datos vivos en una tabla de datos del archivo. El supuesto es que hay un Constraint NOT NULL en una columna de la tabla de destino que no puede haber hecho cumplir en la tabla fuente:
alter table sales_archive modify constraint sa_nn1 disable novalidate;
insert into sales_archive select * from sales_current;
alter table sales_archive modify constraint sa_nn1 enable novalidate;
update sales_archive set channel=’NOT KNOWN’ where channel is null;
alter table sales_archive modify constraint sa_nn1 enable validate;
COMPROBANDO CONSTRAINT
Los Constraints pueden ser comprobados como una declaración es ejecutada (un IMMEDIATE CONSTRAINT) o cuando una transacción es COMMITTED (un DEFERRED CONSTRAINT). Por default, todos los Constraints son IMMEDIATE y NOT DEFERRABLE. Un acercamiento alternativo al ejemplo previo habría sido posible si el Constraint se ha creado como DEFERRABLE.
set constraint sa_nn1 deferred;
insert into sales_archive select * from sales_current;
update sales_archive set channel='NOT KNOWN’ where channel is null;
commit;
set constraint sa_nn1 immediate;
Para el Constraint sea DEFERRABLE, debe haber sido creado con la sintaxis apropiada:
alter table sales_archive add constraint sa_nn1
check (channel is not null) deferrable initially immediate;
No es posible hacer un Constraint DEFERRABLE mas tarde, sino se ha creado de esa manera. El Constraint SA_NN1 por defecto se aplica cuando una fila es insertada (o actualizada), pero el CHECK puede ser post puesto hasta que la transacción sea COMMITS. Un uso común para los Constraint DEFERRABLE está con FOREIGN KEY. Si un proceso inserta o actualiza filas en ambas tablas el padre y el hijo, si el Constraints Foreign Key no se difiere el proceso puede fallar si las filas no se procesan en el orden correcto.
Cambiando el estatus de un Constraint entre ENABLED/DISABLED y VALIDATE/NOVALIDATE es una operación que afectara todas las sesiones. El estado es una actualización del diccionario de datos. Cambiando un Constraint deferrable entre IMMEDIATE y DEFERRED es especifico de la sesión, aun que el estado inicial se aplicara a todas las sesiones.
EJERCICIO 9-2.
GESTIONAR CONSTRAINTS
En este ejercicio, utilice Database Control y SQL PLUs para definir y ajustar algunos Constraints en la tabla creada en el ejercicio 9-1.
1. En Database Control, navegue a la lista de tablas HR, como en el ejercicio 9-1, paso 5 y 6.
2. Seleccione el radio Botón para la tabla EX_EMPS y dar clic en el Botón Editar. Esto le llevara a una ventana que muestra las definiciones de columnas de la tabla EX_EMPS.
3. Tomar la Ficha Constraints para ver los tres Constraints NOT NULL que se crearon con la tabla. Tenga en cuenta que si nombres no son útiles-esto será solucionado en el paso 10.
4. En la lista desplegable Constraint, seleccione Primary y dar clic al botón Agregar.
5. En la ventana Constraints Agregar PRIMARY, elija la columna EMPLOYEE_ID y dar clic en continuar, como en la siguiente ilustración, para volver a la ventana Editar Tabla.
6. Dar clic en el botón Mostrar SQL para ver la declaración de creación de Constraint, y entonces el botón Regresar.
7. Dar clic al botón aplicar para ejecutar la declaración.
8. Conéctese a su base de datos como usuario HR con SQL PLUS.
9. Ejecute esta consulta para encontrar los nombres de los Constraints.
select constraint_name,constraint_type,column_name
from user_constraints natural join user_cons_columns
where table_name='EX_EMPS';
10. Renombre el Contraints a algo mas significativo, utilizando los nombres de restricción original recuperado en el paso 9, con los comandos ALTER TABLE:
ALTER TABLE ex_emps RENAME CONSTRAINT old_name TO new_name ;
La ilustración que sigue muestra este proceso, utilizando una convención de nombres basada en un sufijo para Constraint de llave Primary “_PK” y “_NN” para el Constraint Check NOT NULL.
No hay comentarios:
Publicar un comentario