Contactos
IBSurgeon
Esta página fue traducida automáticamente. Lee el original en inglés. English

Biblioteca de IBSurgeon

Restricciones de base de datos en Firebird e InterBase

NOTICE: Este documento es el capítulo del libro “The InterBase World” que fue escrito por Alexey Kovyazin y Serg Vostrikov.

Este capítulo está dedicado a las restricciones de las bases de datos InterBase y Firebird. Las restricciones de base de datos son reglas que definen las interrelaciones entre tablas y pueden verificar y modificar los datos en una base de datos. Estas reglas se implementan como objetos especiales de base de datos. La principal ventaja de usar restricciones consiste en la capacidad de implementar la verificación de datos y una parte de la lógica de negocio de la aplicación a nivel de base de datos, es decir, centralizarla y simplificarla, haciendo que el desarrollo de aplicaciones de bases de datos sea más fácil y confiable.

Los desarrolladores principiantes a menudo descuidan el uso de restricciones de base de datos, considerando que obstaculizan el trabajo creativo. Sin embargo, en realidad, tal opinión se forma por un conocimiento insuficiente de la teoría y la práctica del diseño de bases de datos.

Al mismo tiempo, los diseñadores más experimentados se atreven a renunciar al uso de algunos tipos de restricciones, gracias a lo cual sus aplicaciones ganan en velocidad. La experiencia de los diseñadores expertos les permite comprender muy bien el funcionamiento del servidor y predecir con precisión su comportamiento en casos complicados, por lo tanto, es mejor que los programadores principiantes de InterBase no recurran a acciones similares de colegas experimentados.

Dentro de este libro no consideramos el diseño de bases de datos, por lo tanto, para obtener más información sobre este tema, consulte la lista de literatura al final del libro. Aquí solo revisaremos todos los tipos de restricciones en la base de datos InterBase y consideraremos ejemplos de su aplicación.

Tipos de restricciones en una base de datos

Existen los siguientes tipos de restricciones en la base de datos InterBase:

  • PRIMARY KEY;
  • UNIQUE KEY;
  • FOREIGN KEY

- pueden activar disparadores automáticos - ON UPDATE y ON DELETE;

  • CHECK

En los capítulos anteriores, mencionamos algunas de estas restricciones, porque era necesario para la presentación lógica del material, pero ahora consideraremos su sintaxis, aplicación e implementación con más detalle. Las restricciones de base de datos son de dos tipos: basadas en un solo campo y basadas en varios campos de la tabla. La sintaxis de ambos tipos de restricciones se proporciona a continuación.

= [CONSTRAINT constraint]

[ …]

= {UNIQUE | PRIMARY KEY

| CHECK ( )

| REFERENCES other_table [( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

}

La sintaxis de las restricciones basadas en varios campos es la siguiente:

= [CONSTRAINT constraint]

[< tconstraint> …]

= {{PRIMARY KEY | UNIQUE} ( col [, col …])

| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK ( )}

La diferencia en la sintaxis entre las restricciones basadas en un solo campo y las basadas en varios campos es obvia: en la última, podemos especificar varios campos incluidos en la restricción. En el caso de una restricción basada en un solo campo, todas las opciones descritas se refieren solo al campo actual. Ciertamente, estos dos tipos de restricciones tienen una forma diferente de aplicación: las restricciones basadas en un solo campo simplemente se agregan a la definición del campo requerido, y las restricciones basadas en varios campos se especifican después de la coma en la definición general de la tabla. En las siguientes partes de este capítulo se dan ejemplos detallados.

Ejemplo de restricción típica

En realidad, las restricciones basadas en un solo campo son un caso especial de las restricciones basadas en varios campos.

A continuación se muestra el ejemplo de creación de una restricción de clave primaria utilizando estos dos enfoques diferentes. Creemos una tabla que contenga solo un campo y establezcamos una restricción de clave primaria en ella.

Aquí está el ejemplo de la clave primaria usando la sintaxis de restricción basada en un solo campo:

CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); En este ejemplo, se crea una clave primaria con el nombre pktest para el campo ID_PK. Como resultado, tenemos una descripción bastante compacta en una sola línea. Podemos usar la sintaxis de restricciones basadas en varios campos para el mismo propósito: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));

Creación de restricciones

Consideremos la creación de restricciones con más detalle. Lo primero en la descripción de la sintaxis común de restricciones es la opción [CONSTRAINT constraint]. Como puede ver, esta opción está entre corchetes, es decir, es opcional.

Usando esta opción, puede establecer el nombre de la restricción creada tanto en el caso de aplicar la sintaxis de restricciones basadas en un solo campo como en el caso de restricciones basadas en varios campos. Si no ha especificado el nombre de la restricción, InterBase lo generará automáticamente. Sin embargo, es mejor establecer el nombre de la restricción creada para mejorar la legibilidad del esquema de la base de datos y simplificar la gestión de restricciones más adelante.

Habiendo establecido el nombre de la restricción, se debe definir su tipo. Consideremos los diferentes tipos de restricciones en el orden en que se indican en la descripción de la sintaxis común de restricciones.

Claves primarias y únicas

Las claves primarias son uno de los principales tipos de restricciones de base de datos. Se aplican para la identificación unívoca de registros en la tabla. Supongamos que almacenamos una lista de personas en una base de datos. Es muy posible que haya dos (o más) personas con el mismo apellido, nombre y patronímico. ¿Cómo podemos distinguir a una persona de otra (ciertamente, la cuestión es distinguir a una persona de otra según la información almacenada en una base de datos)?

En este caso, “persona” está representada por un registro en la tabla, por lo tanto, podemos hacer una pregunta más general: ¿cómo podemos distinguir un registro en (cualquier) tabla de otro registro en la misma tabla? Para este propósito, se utilizan restricciones: claves primarias. La clave primaria representa uno o varios campos en la tabla, cuya combinación es única para cada registro. No hay valores repetidos de una clave primaria para una tabla.

Las claves únicas realizan la misma función: también sirven para la identificación unívoca de registros en la tabla. La diferencia entre claves primarias y únicas es que solo puede haber una clave primaria en la tabla, y en cuanto a las claves únicas, puede haber varias. Se observa que tanto una clave primaria como una clave única pueden usarse como base de referencia para claves foráneas (ver más adelante).

La descripción formal de las nociones de claves primarias y únicas, así como otras definiciones importantes, se puede encontrar en la aplicación “Glosario” al final del libro. La sintaxis de creación de una clave primaria y una clave única basada en un campo único es la siguiente:

< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}

Ejemplos de claves primarias y únicas:

CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*una clave primaria*/

uk1 VARCHAR(50) NOT NULL UNIQUE,/*una clave única */

uk2 INTEGER NOT NULL UNIQUE /\* otra clave única */);

Sintaxis de creación de claves primarias y únicas basadas en varios campos:

= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

Esta sintaxis permite crear claves sobre la base de una combinación de campos. Aquí están los ejemplos de creación de claves primarias y únicas a partir de varios campos:

CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*clave primaria pkt basada en dos campos*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*clave única ukt1 basada en dos campos*/

Preste atención a que todos los campos incluidos en claves primarias y únicas deben declararse como NOT NULL, ya que estas claves no pueden tener un valor indefinido. Además de crear la restricción de claves primarias y únicas al crear la tabla, existe la capacidad de agregar restricciones a una tabla que ya existe. En este caso, se utiliza la sentencia DDL: ALTER TABLE. La sintaxis de agregar restricciones de clave primaria o única a una tabla existente es similar a la descrita anteriormente:

ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

Consideremos el ejemplo de creación de clave primaria y única usando ALTER TABLE:

CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

Luego agregamos las claves. Primero la primaria:

ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);

Luego la única: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);

Cabe señalar que solo el propietario de esta tabla o el administrador del sistema SYSDBA (para más detalles sobre los propietarios y el usuario SYSDBA, consulte el capítulo “Seguridad en InterBase: usuarios, sus funciones y derechos” - parte 4) puede realizar la adición (así como la eliminación) de claves primarias y únicas a la tabla.

Claves foráneas

La siguiente restricción utilizada con frecuencia en bases de datos InterBase es la restricción de clave foránea. Esta es una herramienta muy poderosa para proporcionar integridad referencial en una base de datos, que permite no solo supervisar la presencia de referencias correctas en una base de datos, sino también controlar estas referencias automáticamente.

El punto de crear una clave foránea es el siguiente: si dos tablas sirven para almacenar información interrelacionada, es necesario garantizar que esta interrelación siempre sea correcta. Por ejemplo, el documento “carta de porte” que contiene el encabezado general (fecha, número de la carta de porte, etc.) y un conjunto de registros detallados (descripción de mercancías, cantidad, etc.).

Para almacenar dicho documento, se crean dos tablas en una base de datos: una para almacenar los encabezados de las cartas de porte y la segunda para almacenar el contenido de la carta de porte: registros sobre mercancías y su cantidad. Estas tablas se denominan principal y subordinada, o tabla maestra y tabla de detalle.

Según el sentido común, el contenido de la carta de porte no puede existir sin la presencia de su encabezado. En otras palabras, no podemos insertar un registro sobre la mercancía si no hemos creado el encabezado de la carta de porte, y no podemos eliminar un registro de encabezado si hay registros sobre las mercancías. Para la realización de dicho comportamiento, la tabla de encabezados y la tabla de detalles se unen mediante una restricción de clave foránea.

Consideremos el sentido de establecer restricciones de clave foránea con el ejemplo de las tablas que contienen información sobre cartas de porte. Para este propósito, crearemos dos tablas para almacenar la carta de porte: la tabla TITLE para almacenar el encabezado y la tabla INVENTORY para almacenar la información sobre las mercancías incluidas en la carta de porte.

CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

Preste atención a que hemos definido una clave primaria en la tabla de encabezados basada en el campo ID_TITLE de inmediato. El resto de los campos de la tabla TITLE contienen información trivial sobre el encabezado de la carta de porte: fecha, número, comentario.

Ahora definamos la tabla para almacenar información sobre las mercancías incluidas en la carta de porte:

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

Veamos qué campos se incluyen en la tabla INVENTORY. Primero, está ID_INVENTORY: la clave primaria de esta tabla. Luego sigue el campo entero FK_TITLE que sirve como referencia al identificador ID_TITLE del encabezado en la tabla de encabezados de cartas de porte. Luego siguen los campos ProductName, Kolvo y Positio que describen la descripción de la mercancía, su cantidad y una posición en la carta de porte. El campo FK_TITLE es el más importante para nuestro ejemplo. Si queremos mostrar la información sobre las mercancías de una determinada carta de porte, debemos usar la siguiente consulta, en la que el parámetro mas_ID_TITLE define el identificador del encabezado:

SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE

Virtualmente, en la situación descrita, nada impide llenar la tabla INVENTORY con registros que se refieran a registros inexistentes en la tabla TITLE. Además, nada interfiere con la eliminación del encabezado de una carta de porte ya existente, por lo que los registros sobre las mercancías pueden quedar “sin dueño”. El servidor no prohibirá ejecutar todas estas inserciones y eliminaciones. Por lo tanto, el control sobre la integridad de los datos en una base de datos se coloca completamente en la aplicación cliente. Sin embargo, usted sabe que varias aplicaciones desarrolladas, quizás, por diferentes programadores pueden trabajar con una misma base de datos, lo que puede llevar a una interpretación diferente de los datos y errores. En consecuencia, es esencial establecer la restricción explícita de que solo se pueden colocar en la tabla INVENTORY aquellos registros sobre las mercancías que tengan la referencia correcta al encabezado de la carta de porte. Esto, de hecho, es una restricción de clave foránea que permite insertar solo aquellos valores que están en la otra tabla en los campos incluidos en las restricciones.

Tal restricción puede crearse utilizando una clave externa. Para el ejemplo dado, tenemos que establecer restricciones de clave externa para el campo FK_TITLE y vincularlo con la clave primaria ID_TITLE en TITLE. Podemos agregar una clave externa a una tabla ya existente mediante el siguiente comando:

ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

Con frecuencia, al agregar una clave externa, aparece el error: objeto en uso. El problema es que para crear una clave externa, tenemos que abrir la base de datos en modo exclusivo, de modo que no haya otros usuarios al mismo tiempo. Tampoco debemos referirnos a la tabla modificada, ya que esto puede causar el error de objeto en uso.

Aquí, INVENTORY es el nombre de la tabla para la cual se establece la restricción de clave externa; fktitle1 es el nombre de la clave externa; FK_TITLE son los campos que forman la clave externa; TITLE es el nombre de la tabla que proporciona los valores (la base de referencia) para la clave externa; ID_TITLE son los campos de la clave primaria o única en la tabla TITLE, que sirven como base de referencia para la clave externa. La sintaxis completa de la restricción de clave externa (con la posibilidad de crear restricciones basadas en varios campos) se muestra a continuación:

= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Como puede ver, las definiciones contienen un gran conjunto de opciones. Para empezar, consideremos una definición básica de clave externa, que se usa con mayor frecuencia en bases de datos reales, y luego analizaremos las opciones posibles.

La forma declarativa de una restricción de clave externa se usa más a menudo cuando se especifica un conjunto de campos (col [, col …]), que formarán la restricción; así como la other_table que contiene una lista de valores posibles para la clave externa en los campos [(other_col [, other_col …])].

Aquí está el ejemplo de dicha definición al crear la tabla:

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);

Tenga en cuenta que en esta definición se omiten las palabras clave FOREIGN KEY, y se implica que el único campo FK_TITLE se usará como clave externa. Una forma más completa de crear la clave externa simultáneamente con la tabla se muestra en el siguiente ejemplo:

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);

Uso de NULL en campos de una clave externa

En los campos sobre la base de los cuales se crea una clave externa, se permite aplicar campos NULL. Esta posibilidad se añade para permitir referencias mutuas. Por ejemplo, si hay dos tablas que se refieren entre sí mediante claves externas. Si no permitimos la referencia vacía (es decir, NULL) en estas claves externas, será imposible agregar cualquier registro a las tablas unidas: para agregar un registro a la primera tabla, se requiere tener un registro en la segunda tabla, y viceversa.

El uso de NULL como referencia vacía permite crear referencias mutuas de dos tablas con referencia cruzada, y también almacenar estructuras jerárquicas en tablas relacionales; en ese caso, los nodos raíz se refieren a registros “vacíos” (es decir, simplemente contienen NULL).

Capacidades ampliadas de soporte de integridad referencial mediante clave externa

Normalmente, la variante declarativa de una restricción de clave externa es suficiente; el servidor solo vigila que sea imposible insertar valores incorrectos en la tabla con la clave externa o, si se intenta hacerlo, aparece un error. Pero InterBase permite ejecutar un conjunto de operaciones automáticas al modificar/eliminar una clave externa. Para este propósito, se utiliza el siguiente conjunto de opciones de clave externa:

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Estas opciones permiten definir diferentes operaciones al actualizar o eliminar valores de clave externa.

Por ejemplo, podemos establecer que al eliminar una clave primaria en la tabla maestra, todos los registros con la misma clave externa en la tabla subordinada se eliminen. En este caso, tenemos que definir una clave externa de la siguiente manera:

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

En realidad, para la implementación de estas operaciones existe un trigger de sistema que ejecuta ciertas operaciones. En la tabla 1.2 se describe las operaciones de diferentes opciones (tenga en cuenta que las opciones NO ACTION|CASCADE|SET DEFAULT|SET NULL no se pueden usar en una misma cláusula ON XXX).

Tabla 1.2
Evento Operación
NO ACTION CASCADE SET DEFAULT SET NULL
ON DELETE Al eliminar una clave externa, no hacer nada: se usa por defecto Al eliminar, eliminar todos los registros relacionados de la tabla subordinada Al modificar,
establecer un campo de clave externa como valor predeterminado		 Al modificar,
establecer un campo de clave externa a NULL
ON UPDATE Al modificar, no hacer nada: se usa por defecto Al modificar un registro, modificar todos los registros relacionados en la tabla subordinada Al eliminar, establecer
un campo de clave externa como valor predeterminado		 Al eliminar,
establecer un campo de clave externa a NULL

Si no especificamos nada o especificamos NO ACTION, debemos encargarnos de cambiar una clave externa (en caso de cambiar una primaria) por nuestra cuenta, y al eliminar la clave primaria debemos eliminar los registros de la tabla subordinada de antemano. Sea muy cuidadoso al usar la opción CASCADE: su uso descuidado puede llevar a eliminar una gran cantidad de registros relacionados.

Restricción CHECK

Una de las restricciones más útiles en una base de datos es la restricción check. Su función es muy simple: verificar el valor insertado en la tabla según cualquier condición y, de acuerdo con el cumplimiento de esta condición, insertar los datos o no. Su sintaxis es bastante simple:

= [CONSTRAINT constraint] CHECK ( )}

Aquí constraint es el nombre de la restricción; es una condición de búsqueda, en la cual el valor insertado/actualizado puede usarse como parámetro. Si la condición de búsqueda se cumple, se permite insertar/actualizar este valor; si no se cumple, aparece un error. El ejemplo más simple de check:

create table checktst( ID integer CHECK(ID>0));

Este check determina si el valor insertado/actualizado del campo ID es mayor que cero y, según el resultado, permite insertar/actualizar un nuevo valor o informa del error (consulte el capítulo “Capacidades ampliadas del lenguaje de procedimientos almacenados de InterBase” (parte 1)).

También hay variantes más complicadas de checks. La sintaxis completa de la condición de búsqueda es la siguiente:

= {

{ | ()}

| [NOT] BETWEEN AND

| [NOT] LIKE [ESCAPE ]

| [NOT] IN ( [ , …] | )

| IS [NOT] NULL

| {[NOT] {= | < | >} | >= | <=}

{ALL | SOME | ANY} ()

| EXISTS ( )

| SINGULAR ( )

| [NOT] CONTAINING

| [NOT] STARTING [WITH]

| ()

| NOT

| OR

| AND }

Por lo tanto, CHECK ofrece un gran conjunto de opciones para verificar valores insertados/actualizados. Debe recordar las siguientes limitaciones al usar CHECK:

  • Los datos para CHECK se toman solo del registro actual. No debe tomar datos para la expresión en CHECK de otros registros de la misma tabla: pueden ser modificados por otros usuarios.
  • Un campo puede tener solo una restricción CHECK.
  • Si para la definición de un campo se usa un dominio que tiene una restricción CHECK de dominio, no se puede redefinir a nivel de un campo concreto en la tabla. Debe decirse que CHECK se implementa mediante triggers de sistema, por lo tanto, debemos ser más cautelosos al usar condiciones muy largas, que pueden ralentizar fuertemente los procesos de inserción y actualización de registros.

Eliminación de restricciones

Muy a menudo, eliminamos diferentes restricciones por las más diversas razones. Para eliminar una restricción, debemos usar la sentencia ALTER TABLE de la siguiente forma: ALTER TABLE tablename DROP CONSTRAINT constraintname

constraintname es el nombre de la restricción que debe eliminarse. Si se especificó un nombre determinado al crear la restricción, debemos usarlo; pero si no, tenemos que abrir cualquier herramienta de administración de InterBase, buscar todas las restricciones relacionadas y averiguar qué nombre de sistema generó InterBase para la restricción requerida.

Cabe señalar que solo el propietario de la tabla o el administrador del sistema SYSDBA pueden eliminar las restricciones.