Oracle Restricciones De Integridad

Transcript

Las Restricciones de Integridad en ORACLE Sánchez E Tejadillos E. Facultad de Informática – Universidad Politécnica de Valencia email: [email protected], [email protected] Resumen Las restricciones de integridad son propiedades de la base de datos que se deben satisfacer en cualquier momento. Oracle es un sistema de gestión de base de datos (SGBD) relacional que permite la definición de restricciones de integridad dentro del diseño de su base de datos al ser creada. Se estudiará los diferentes tipos de restricciones que comprenden los siguientes puntos:  Tratamiento de valores nulos.  Valores por defecto.  Integridad de clave primaria.  Claves alternativas.  Integridad referencial.  Restricciones de integridad estáticas. Estas restricciones serán definidas en la fase de diseño de la base de datos e incluidas en la los scripts de creación de tablas. Otra cualidad del sistema de gestión de Oracle es la posibilidad de modificar las restricciones definidas para una tabla. Esto puede llevar conllevar a “inconsistencia” de los datos ya introducidos en la base de datos. Por ello, Oracle tiene definidos mecanismos para modificación de los datos ya existentes. Además se estudiará cuando son ejecutas las restricciones de integridad dentro del esquema de funcionamiento de una base de datos Oracle. 1. Introducción La integridad de los datos es la propiedad que asegura que información dada es correcta, al cumplir ciertas aserciones. Las restricciones de integridad aseguran que la información contenida en una base de datos es correcta. 1 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia En los primeros puntos del trabajo, se realizará una definición a modo general de lo que son las restricciones de integridad, los tipos y ventajas que tiene su implementación en el esquema de la base de datos. La segunda parte está orientada más a la parte práctica. Se especifica más concretamente las definiciones y modificaciones que se pueden realizar sobre restricciones de integridad en ORACLE. Para todos y cada uno de los puntos se especificará el formato de la cláusula correspondiente en ORACLE y un ejemplo de la misma. La modificación de las restricciones de integridad en ORACLE tan sólo se puede implementar a través de la cláusula ALTER TABLE. 2. Definición de restricciones 2.1 Descripción general La evolución en el tiempo de una Base de Datos puede describirse por una secuencia de estados. Dato un estado D, su sucesor D’, se obtiene aplicando a D una transacción T, tal que D  T  D’ [1]. Las restricciones de integridad aseguran que la información contenida en la base de datos cumple ciertas restricciones para los diferentes estados. Existen dos tipos de restricciones:  Estáticas: limitan los estados permitidos de la Base de Datos.  Dinámicas: restringen las posibles transiciones de estados de la base datos. Para incorporar el tratamiento de las restricciones de integridad en el sistema pueden realizarse:  Añadiendo código adicional para verificar y asegurar que se cumplen las restricciones.  Declarando las restricciones como parte del esquema de la base de datos. La definición en la fase de diseño de las restricciones de integridad proporciona mayor número de ventajas, ya que:  Reduce el coste de desarrollo de software. 2 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia   Es más confiable al ser centralizado y uniforme. Mantenimiento más fácil. En las siguientes secciones se describirán más ampliamente la definición de las restricciones de integridad a modo general, así como los tipos de restricciones que podemos encontrar y otras clasificaciones. También se describirá como se implementa el tratamiento de las restricciones en ORACLE, declarándolas como parte del esquema de la base de datos en la fase de creación de las tablas y cómo se pueden ser modificadas con posterioridad. 2.2 Representación de restricciones de integridad estáticas.  Restricciones de Cardinalidad: se restringe la cantidad de objetos que puede referenciar a través de un atributo o la cantidad de objetos agregados que lo pueden referenciar como elemento de la agregación. Incluye el control de permiso para que un atributo pueda incluir un indicador definido por el sistema, como es el indicador NULL y que represente información faltante.  Restricción de Dominio: sólo los objetos especificados pueden servir como dominio de un atributo.  Restricción de Unicidad: un objeto podrá ser unívocamente identificable usando un determinado atributo.  Restricción de Inverso: si un Atributo A1 es el inverso de otro A2, entonces si O2 es un valor del atributo A1 de algún objeto O1 implica que O1 es un valor del atributo A2 para el objeto O2.  Restricción de Coexistencia: una instancia de la clase hija debe ser también existir como instancia de sus clases padre.  Restricción de Clases hijas Disjuntas: las instancias de la clase padre sólo pueden pertenecer a una clase hija dentro de la jerarquía.  Restricción de Cobertura: todas las instancias de al menos una clase hija dentro de la jerarquía. 2.3 Comprobación de la integridad. 3 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia La comprobación de la integridad consiste en asegurar que un par de estados (D,D’), implicados en una transacción, satisface las restricciones dinámicas y que el estado final D’ satisface las restricciones estática /dinámicas. La comprobación de la integridad dentro de ORACLE, se realiza mediante el algoritmo OR, donde se define la ejecución del evento intercalado con la ejecución de las reglas activadas por él y la comprobación de las restricciones de integridad relevantes para el evento. 2.4 Restricciones de integridad elementales.  Integridad de Entidades: ningún componente de clave primaria de una relación puede aceptar nulos. Nulo se referirá a información faltante. El concepto de unicidad es parte de la definición de clave primaria en sí.  Integridad Referencial: la base de datos no puede contener valores de clave ajena sin concordancia. La posibilidad de aceptación de valores nulos deberá ser evaluada por el analista, al igual de las opciones a seguir (restricción, propagación o anulación) En el caso de ORACLE, todas las definiciones de restricciones (tratamiento de nulos, claves primarias, ajenas etc.) ya sean a nivel de columna o a nivel de tabla, tienen un nombre. Como esta asignación de nombre por parte del diseñador es opcional, si no se le asigna nombre en el momento de la definición, ORACLE le asigna uno interno. En cualquier caso, es conveniente declarar una restricción con su nombre porque así es posible referenciarla posteriormente en sentencias ALTER TABLE, para activarla o desactivarla. A continuación se indicarán cómo son tratadas las distintas variantes de las restricciones de integridad elementales dentro de ORACLE. 2.5 Tratamiento de valores nulos en ORACLE. Esta restricción especifica si una columna puede contener o no valores nulos. ORACLE por defecto se asume que la columna admite valores nulos. Formato: atributo tipo [CONSTRAINT nombre] NOT NULL | NULL. 4 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia Ejemplo: Dni integer CONSTRAINT nn_dni NOT NULL 2.6 Tratamiento de los valores por defecto en ORACLE. En este caso, las restricciones de integridad se utilizan para especificar valores que serán asignados a una columna, cuando en ésta no se introduzca ningún valor. El valor especificado en este campo debe ser del mismo tipo que la columna. Las restricciones se especifican como parte de la definición de la columna. Para el tratamiento de información faltante, se le puede especificar un NULL como valor por defecto siempre que la columna permita nulos. En la expresión no se puede hacer referencia a otras columnas. Formato: atributo tipo DEFAULT expresión. Ejemplo: Num integer DEFAULT 1+3. 2.7 Tratamiento de clave primaria en ORACLE En este caso la restricción de integridad designa una columna o combinación de columnas como clave primaria de la tabla. Para facilitar su especificación, ORACLE permite definir la restricción a nivel de columna (en el ámbito de definición de una columna) o a nivel de tabla (si se definen después de introducir todas las columnas, en una sección genérica final de definición de restricciones). - A nivel de columna: Se define dentro de una constraint asociada a la misma. La definición de una clave primaria lleva implícita la restricción de valores no nulos. La misma columna puede ser definida como primaria y ajena pero no como primaria y alternativa. - A nivel de tabla: Se utiliza para definir claves primarias compuestas. Se declara antes o después de haber introducido las columnas de la tabla en cuestión. Formato: 5 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia A nivel columna: atributo tipo [CONSTRAINT nombre] PRIMARY KEY. A nivel tabla: [CONSTRAINT nombre] PRIMARY KEY(colum1,.. colum ..). Ejemplo: A nivel columna: Dni integer [CONSTRAINT pk_dni] PRIMARY KEY A nivel tabla: CONSTRAINT pk_nomapelli PRIMARY KEY(nom,apelli). Por defecto, ORACLE no permite la inserción de valores nulos en las columnas definidas como clave primaria. 2.8 Tratamiento de claves alternativas en ORACLE Para las claves alternativas en ORACLE, se designa una columna o combinación de columnas como clave única alternativa a la clave seleccionada como primaria. Existen dos posibilidades para seleccionar una clave primaria, - Para definir una columna como única se especifica dentro de una constraint asociada a dicha columna. Formato: atributo tipo [CONSTRAINT nobmre] UNIQUE. Ejemplo: nombre char CONSTRAINT unq_nombre UNIQUE. - Para definir una combinación de columnas como clave única se especifica dentro de una constraint asociada a la tabla. Formato: [CONSTRAINT nombre] UNIQUE (columan1, columana2 ...) Ejemplo: CONSTRAINT unq_nomapell UNIQUE(nombre, apellidos) 2.9 Tratamiento de claves ajenas en ORACLE 6 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia En ORACLE pueden definirse claves ajenas a nivel columna o a nivel de tabla. La integridad referencial de claves ajenas a nivel columna se declara detrás del tipo asociado al atributo en cuestión, con el formato presentado a continuación, en el que la palabra columna representa la clave primaria o única referenciada en la tabla n_tabla. Si no se especifica la columna o columnas, sino tan sólo el nombre de la tabla, se asume por defecto que estamos referenciando a su clave primaria. A diferencia de la columna a la que se hace referencia, la columna donde se define el REFERENCES puede tener valores nulos. A acciones a realizar: 1. Restringir (restrict), 2. poner a nulos (set null), 3. propagar (cascade), Si la clave primaria a la que hace referencia la clave ajena es borrada o actualizada, en este caso sólo podemos especificar la situación en que se borre la clave primaria y queramos propagarlo a la clave ajena. Esto es realizado mediante la cláusula on delete cascade. Si ésta no aparece, al igual que para actualizaciones de la clave primaria, la operación se asume restringida. Para incluir esas características faltantes hay que generar (es decir, programar) los disparos correspondientes. Formato: atributo tipo [CONSTRAINT nombre] REFENCES ntabla[(columna)] [ON DELETE CASCADE] Ejemplo: Dep char(10) REFERENCES departamento(nom) Si las restricciones se definen a nivel de tabla se especifica la columna o composición de columnas que forman parte de la clave ajena después de la palabra clave FOREIGN KEY y la columna o columnas a la que se hace referencia después de la palabra clave REFERENCES. Obviamente, el número de columnas y tipos de la clave ajena debe coincidir con el número de columnas y tipos de la clave primaria referenciada. 7 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia Formato: [CONSTRAINT nombre] FOREIGN KEY (columna1, columna2, ...) REFERENCES n_tabla[(columna1, columna2,...)] [ON DELETE CASCADE] Ejemplo: FOREIGN KEY(dep) REFERENCES departamento(nom) 2.10 Tratamiento de las restricciones de integridad estáticas en ORACLE Las restricciones de integridad estáticas serán aquellas fórmulas bien formadas de primer orden construidas con atributos de la tabla como términos básicos, que depende satisfacerse en todos los estados válidos para las tuplas de una relación. Estas restricciones de integridad están especificadas en ORACLE dentro de una restricción de tipo CHECK asociada a una columna o una tabla. La implementación de esta restricción en una columna sería: Formato: atributo tipo [CONSTRAINT nombre] CHECK condición. Ejemplo: Saldo integer CHECK saldo!=saldo_base A diferencia de otros gestores, en esa condición se puede hacer referencia a cualquier columna de la tabla. No se pueden introducir en estas condiciones atributos de otras tablas. En ese caso, sólo queda el recurso de programar explícitamente la restricción de integridad, lo que habitualmente se hace generando disparos (“triggers”) de la base de datos. La implementación a nivel de tabla sería equivalente. 3. Modificación de restricciones Una vez creada una tabla es inevitable enfrentarse a situaciones en las que cambios no previstos obliguen a modificar las restricciones de integridad declaradas en el momento de su creación. La flexibilidad proporcionada por un SGBD para abordar este tipo de modificaciones es un factor diferenciador que puede resultar básico. 8 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia Se trata de un problema clásico en Base de Datos, al que se le conoce como Gestión de Evolución de Esquemas. Entre las modificaciones a que está expuesta una base de datos en explotación, es importante distinguir entre: - Aquellas que no afectan a la integridad de los datos ya existentes (por ejemplo, añadir una columna nueva a una tabla, o permitir que admita nulos un atributo que hasta ahora no los admitía). - Y las que sí afectan a la integridad de los datos e implican toma de decisiones (por ejemplo, cambiar una restricción de integridad: la cuestión que se plantea es qué hacer con los datos preexistentes que no la cumplen, ¿borrarlos? ¿ignorarlos y aplicar la restricción a partir del momento de su introducción en el esquema?). Las elección del mecanismo a seguir para mantener la base de datos íntegra deberá ser elegida por el diseñador de la base de datos, según considere oportunas. Estas comprobaciones se realizarán mediante disparos o reglas de actividad definidas también en la base de datos. La sentencia ALTER TABLE sirve para añadir o redefinir una columna, añadir o borrar una restricción de integridad y para activar o desactivar cualquier restricción de integridad o disparo. Su implementación en ORACLE sería la siguiente: Formato: ALTER TABLE [esquema.]nom_tabla [ADD nom_col + defe_de_restriccion_col] [MODIFY nom_col tipo + def_de_restriccion_col] [ADD + def_restriccion_tabla] [DROP PRIMARY KEY | UNIQUE | CONSTRAINT nombre [CASCADE]] [ENABLE PRIMARY KEY | UNIQUE | CONSTRAINT nombre | ALL TRIGGERS] [DISABLE PRIMARY KEY | UNIQUE | CONSTRAINT nombre [CASCADE]] donde: - def_de_restriccion_col y def_restriccion_tabla se refieren a las declaraciones de restricciones que se pueden realizar en el ámbito de una columna o de la tabla en su conjunto respectivamente. 9 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia - Si deshabilitamos una restricción tenemos la posibilidad de deshabilitar en cascada todas las restricciones que dependan de ella a través de la cláusula cascade. - Si habilitamos una restricción de integridad referencial, la restricción de clave primaria o única asociada debe ser habilitada con anterioridad (si no lo estaba ya). - Las opciones asociadas a UNIQUE identifican la clave única implicada en la modificación indicándola entre paréntesis. La cláusula ENABLE ALL TRIGGERS permite activar disparos que puedan haber sido desactivados con la orden ALTER TRIGGER opción DISABLE proporcionada por ORACLE. 4. Mecanismo de activación de las restricciones de integridad en ORACLE. La ejecución de los eventos en una base de datos ORACLE viene definida por el Algoritmo A2 or y OR. Algoritmo A2or recursivo: Mientras existan reglas activadas 1. seleccionar una regla activada R 2. comprobar la condición de R 3. si la condición de R es cierta: a. ejecutar la acción de R b. ejecutar OR para las reglas activadas por la acción de R fin mientras El algoritmo OR indica como se intercala la ejecución de las reglas activadas por él y la comprobación de las restricciones de integridad relevantes para el evento. 1. Procesar con A2or las reglas activadas de tipo BEFORE evento FOR EACH STATEMENT. 2. Para cada tupla afectada por el evento a. Procesar con A2 or las instancias (para la tupla actualizada= de las reglas activadas de tipo BEFORE evento FOR EACH ROW b. Ejecutar la actualización sobre la tupla y comprobar las restricciones de integridad relevantes para la tupla actualizada. 10 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia Aquí se comprueba las restriciones de integridad relevantes par ael evento que son comprobadas a nivel de tupla individual (NOT NULL, CHECK(), ...). c. Procesar con A2or las instancias (para la tupla actualizada ) de las reglas activadas de tipo AFTER evento FOR EACH ROW 3. Comprobar las restricciones de integridad relevantes para la operación de actualización a nivel de sentencia SQL (FOREING KEY). 5. Procesar con A2or las reglas activadas de tipo AFTER evento FOR EACH STATEMENT. 6. Ejemplo práctico sobre una base de datos común. El diseño de las tablas de esta pequeña base de datos vendría dado por: CREATE TABLE Departamento (codigo CHAR(5) NOT NULL, nombre VARCHAR(100) NOT NULL, director VARCHAR(50), telefono CHAR(11), CONSTRAINT CP_dpto PRIMARY KEY (codigo); Para la tabla “departamento” tiene definidas las restricciones de integridad de:  tratamiento de no nulos para las columnas:  código  nombre  tratamiento de clave primaria denominada CP_dpto para la columna código CREATE TABLE Profesor (codigo CHAR(5) NOT NULL, nombre VARCHAR(100) NOT NULL 11 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia direccion VARCHAR(50), telefono CHAR(11), categoria CHAR(3) NOT NULL CONSTRAINT RI_cat CHECK (categoria=’TEU’ OR categoria=’CU’ OR categoria=’CEU’), dpto CHAR(5), CONSTRAINT CP_prof PRIMARY KEY (codigo), CONSTRAINT Caj_prof_dpto FOREIGN KEY (dpto) REFERENCES Departamennto); Como puede verse, la tabla “profesor” tiene definidas las restricciones de integridad de:  tratamiento de no nulos para las columnas:  código  nombre  categoría  restricciones de integridad estáticas con la cláusula check llamada RI_cat para la columna categoría, que delimita el rango de valores que puede contener la columna a tres cadenas de caracteres, ‘TEU’, ‘CU’ y ‘CEU’.  tratamiento de clave primaria denominada CP_prof para la columna código  tratamiento de clave ajena denominada Caj_prof_dpto de la columna dpto haciendo referencia a la tabla departamento. CREATE TABLE Asignatura (codigo CHAR(5) NOT NULL, nombre VARCHAR(100) NOT NULL, cre_teo NUMBER(3,1) NOT NULL CONSTRAINT RI_teo CHECK (cre_teo>0), cre_pra NUMBER(3,1) NOT NULL CONSTRAINT RI_teo CHECK (cre_pra>0), dpto CHAR(5), CONSTRAINT CP_asg PRIMARY KEY (codigo), CONSTRAINT Caj_asg_dpto FOREIGN KEY (dpto), REFERENCES Departamento); La tabla “asignatura” tiene definidas las restricciones de integridad de:  tratamiento de no nulos para las columnas:  código  nombre  cre_teo  cre_pra  restricciones de integridad estáticas con la cláusula check para las columnas: 12 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia    cre_teo (denominada RI_teo)  cre_pra (denominada RI_pra) tratamiento de clave primaria denominada CP_asg para la columna código tratamiento de clave ajena denominada Caj_asg_dpto de la columna dpto haciendo referencia a la tabla departamento. CREATE TABLE Docencia (cod_prof CHAR(5) NOT NULL, cod_asg CHAR(5) NOT NULL, creditos NUMBER(3,1) NOT NULL CONSTRAINT RI_cre CHECK (creditos>0), CONSTRAINT CP_doc PRIMARY KEY (cod_prof, cod_asg), CONSTRAINT Caj_doc_prof FOREIGN KEY (cod_prof) REFERENCES Profesor, CONSTRAINT Caj_doc_asg FOREIGN KEY (cod_asg) REFERENCES Asignatura); Finalmente, la tabla “docencia” tiene definidas las restricciones de integridad de:  tratamiento de no nulos para las columnas:  cod_prof  cod_asg  créditos  restricciones de integridad estáticas con la cláusula check denominada RI_cre para la columna créditos.  tratamiento de clave primaria denominada CP_doc para la combinación de columnas cod_prof y cod_asg.  tratamiento de clave ajena denominada CAj_doc_prof de la columna cod_prof haciendo referencia a la tabla profesor.  Tratamiento de clave ajena denominada CAj_doc_asg de la columna cod_asg haciendo referencia a la tabla asignatura. 7. Conclusiones Finalmente, podemos establecer que la inclusión de las restricciones de integridad dentro del diseño del esquema de la base de datos se traduce en ventajas. ORACLE da muchas posibilidades a la hora de implementar los distintos tipos de restricciones de integridad que se pueden dar sobre una base de datos. Las restricciones de entidades son definidas al mismo tiempo que son creadas las tablas. Las restricciones referenciales se definen primeramente en la creación de las tablas, y 13 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia su mecanismo de validación de datos puede hacerse mediante disparos (triggers). Los disparos (triggers) es un punto importante dentro de las restricciones de integridad que no ha sido desarrollado en este trabajo. La razón de no profundizar sobre los disparadores es debido a que otros compañeros nuestros son los encargados de desarrollar el tema. 8. Referencias [1] Pastor O., Blesa P., Gestión de Bases de Datos, Servicio de Publicaciones UPV-2000.4171 [2] Oracle Corporation, Oracle 7 Server Concepts Manual. [3]www.dsic.upv.es/asignaturas/facultad/bdv. Transparencias del 2000/2001 de la asignatura Base de Datos Avanzadas. curso de 14 Laboratorio de Sistemas de Información Facultad de Informática Universidad Politécnica de Valencia