Tablas. Claves primarias y generadores

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

InterBase es un DBMS relacional. Además, esto significa que todos los datos en InterBase se almacenan como tablas. La tabla, tal como se realiza desde el punto de vista de SQL, es muy similar a la tabla ordinaria que se puede dibujar a mano en una hoja de papel o crear en un programa como Microsoft Excel. Las tablas en InterBase tienen columnas y filas donde se colocan los datos. La tabla necesariamente tiene un nombre, único dentro de una base de datos. Las tablas son el principal almacenamiento de información en una base de datos y, por lo tanto, debe tener mucho cuidado al crearlas.

Existen reglas que describen cómo crear tablas en la base de datos relacional, reflejando los datos del mundo real y, al mismo tiempo, permitiendo organizar un almacenamiento eficaz de la información en una base de datos. El proceso de aplicación de estas reglas para diseñar una base de datos “correcta” se llama normalización. Hemos citado a propósito la palabra “correcta”, porque “base de datos normalizada” y “base de datos optimizada” no son sinónimos. No tiene que cumplir las reglas de normalización de manera inequívoca: siempre aplique la corrección para la especificación de un problema determinado.

La normalización de tablas en una base de datos se considera en detalle en el libro [14. y, por lo tanto, no intentaremos abarcar lo inabarcable y volveremos a nuestro tema de discusión: las tablas de InterBase. Consideremos la sintaxis de la sentencia DDL (DDL - Data Definition Language, para más detalles ver el glosario) que permite crear tablas:

CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);

Aquí table es un nombre de la tabla creada, - la descripción de las columnas (a veces diremos - campos) de la tabla creada. La opción table [EXTERNAL [FILE] “”] significa que se creará la llamada tabla externa, que no se almacena en un archivo de base de datos compartido, sino en un archivo separado con un nombre . Como puede ver, todo es simple: definimos un nombre de tabla y las columnas que contiene. Ahora consideraremos en detalle cómo definir columnas. La sintaxis de creación de una columna se describe mediante la siguiente sentencia DDL:

= col { datatype | COMPUTED [BY] (< expr>)} | domain}

[DEFAULT { literal | NULL | USER}]

[NOT NULL] [ ]

[COLLATE collation]

Esta es una definición bastante grande, sin embargo, en la definición de una columna solo una pequeña parte de las sentencias dadas es obligatoria. Cada columna en la tabla debe tener un nombre, único dentro de la tabla, así como un tipo de datos definido por la sentencia datatype, o una expresión para calcular el valor de una columna (para columnas calculadas), o el dominio (ver más abajo), definido domain. Los tipos de datos se consideraron en el capítulo “Tipos de datos”; por lo tanto, puede comprender fácilmente cómo se forma la expresión SQL para crear una tabla.

Conectémonos a nuestra base de datos FIRSTBASE.gdb creada anteriormente en el capítulo “Crear una base de datos”, y probemos a trabajar con tablas en la práctica. Cuando se trata de crear, eliminar y actualizar tablas, cualquiera de las herramientas administrativas de InterBase (de las enumeradas en la aplicación “Herramientas de administrador y desarrollador de InterBase”), así como la utilidad estándar isql.exe de un conjunto de suministro de cualquier clon de InterBase, será adecuada.

Aquí hay un ejemplo de una tabla simple llamada TABLE_EXAMPLE que contiene 3 campos de varios tipos:

CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);

Esta tabla ilustra el caso más frecuente en el proceso de desarrollo de una base de datos. Sin embargo, también hay otros métodos para definir campos. Por ejemplo, podemos establecer un tipo de campo usando dominios. El dominio es un tipo definido por un usuario para la conveniencia de aplicar ciertas combinaciones de parámetros de tipo. Por ejemplo, es posible definir el dominio D_ID para especificar campos de identificadores. Habiendo definido el dominio, podemos usarlo para establecer el tipo de un campo:

CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);

El campo ID tendrá el tipo definido por el dominio D_ID. Así, habiendo definido el tipo del campo en el dominio, las comprobaciones y restricciones requeridas, podemos aplicar este dominio muchas veces para crear campos de la misma función. Por ejemplo, monetario sin copias tediosas y que conllevan el peligro de equivocarse al copiar definiciones de tipos de variables. La tercera forma de establecer una columna en la tabla es definirla como calculada (COMPUTED BY) y especificar una condición según la cual se calculará su valor. Por ejemplo, podemos desear tener una columna que calcule el 10 % del valor del campo PRICE_1 en nuestra tabla. En este caso, se debe escribir el siguiente comando:

CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));

Pero no piense que ahora, tan pronto como insertemos los datos en el campo PRICE_1, en el campo PRICE_10 habrá una décima parte del valor de este campo. No, el proceso aquí es más complicado. En realidad, obtendremos la décima parte requerida solo al referirnos al campo PRICE_10, por ejemplo, al ejecutar una consulta SELECT a esta tabla. Es decir, no se almacenan datos en un campo calculado, y se realiza el cálculo de la expresión conectada con un campo, y el resultado se produce como respuesta a la consulta.

Entonces, hemos considerado 3 formas principales de especificar campos en la tabla. Ahora consideremos en detalle las opciones que se pueden establecer al crear una columna. La opción [DEFAULT {literal | NULL | USER}] - permite establecer un valor de columna por defecto. Es muy conveniente para el llenado automático de datos. Hay 3 formas de establecer un valor por defecto. La primera se designa como literal y permite establecer valores por defecto como constantes de texto, números o fechas. Por ejemplo, podemos generar las siguientes expresiones para crear una columna con valores por defecto de texto: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’

Así, todos los campos insertados en la tabla tomarán valores por defecto, es decir, si no se ha definido otro valor para el campo NAME, aparecerá la cadena ‘Vasily Stanislavovich’. La segunda forma de establecer un valor por defecto es especificar DEFAULT NULL en la definición de una columna. Y en los registros creados nuevamente, el valor de esta columna será NULL, si el otro valor, ciertamente, no se ha establecido explícitamente. Un ejemplo:

PRICE_1 DOUBLE PRECISION DEFAULT NULL

La tercera forma de establecer un valor por defecto es especificar DEFAULT USER en la definición de una columna. Así, en los registros creados nuevamente, este campo contendrá el nombre del usuario actual, es decir, el usuario que hizo una conexión con InterBase y ejecutó esta inserción (para más detalles sobre usuarios ver el capítulo “Seguridad en InterBase: usuarios, sus funciones y derechos” (parte 4)). Para algunos campos es esencial que el campo tenga un valor no vacío. Por ejemplo, un campo que no puede estar vacío según la especificación del problema. Para establecer una restricción a nivel de base de datos de que un campo debe tener un valor definido, es necesario hacer la siguiente adición a la descripción de una columna:

NAME VARCHAR(80) NOT NULL

Así, habrá un campo en el que no se pueden almacenar valores nulos. Generalmente, la restricción NOT NULL se combina con la opción DEFAULT que asigna definitivamente un valor correcto a este campo. Pero frecuentemente la restricción NOT NULL no es suficiente. Por ejemplo, en el caso de almacenar precios en una base de datos, está bastante claro que no pueden tomar valores negativos (aunque sería genial si nos pagaran extra al comprar bienes). Para hacer que el servidor verifique los valores de los precios insertados en una base de datos bajo una condición de positividad, es necesario definir una columna de la siguiente manera:

PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)

Los valores insertados en la columna PRICE_1 se verificarán bajo una condición de positividad. Cabe señalar que diferentes opciones consistentes se pueden combinar, y por ejemplo, podemos establecer un valor no vacío y verificar la positividad:

PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)

Al crear las columnas, algunas opciones no se pueden combinar, por ejemplo, es imposible establecer NULL por defecto y simultáneamente una restricción de valor no vacío. Debe marcarse que las comprobaciones pueden realizar un conjunto de funciones útiles en la gestión de datos en una base de datos. Consideraremos su uso en detalle en el capítulo “Restricciones de base de datos”.

Entonces, hemos considerado las formas de crear tablas y campos con diferentes opciones. Sin embargo, hay casos en los que tenemos que alterar la tabla que ya existe. Ciertamente, podemos recrear la tabla por completo. Primero, debemos ejecutar el comando de eliminar la tabla y luego crearla nuevamente. Por ejemplo:

DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);

Pero tal forma de alterar tablas tiene inconvenientes significativos. Al eliminar una tabla usando el comando DROP, todos los datos que contiene la tabla se eliminan y, para no perderlos, es necesario copiarlos a tablas temporales. Es bastante problemático. Por lo tanto, existe el comando ALTER TABLE para la alteración fácil de la estructura de tablas que permite agregar nuevos campos, eliminar existentes, así como agregar/eliminar restricciones de integridad referencial.

Por ejemplo, queremos agregar una columna más a la tabla destinada a almacenar los datos sobre el patronímico de una persona:

ALTER TABLE Table_example ADD Patronimic VARCHAR(80);

Después de la ejecución de este comando, nuestra tabla Table_example tendrá una nueva columna con el nombre Patronimic y tipo VARCHAR (80). Si queremos eliminar una columna con el nombre NAME de la tabla, debemos ejecutar lo siguiente:

ALTER TABLE Table_example DROP Name;

Puede ver la sintaxis completa de la sentencia ALTER TABLE en [1.. Es un comando muy útil, y lo usaremos a menudo.

¿Y qué hacer, preguntará, si es necesario modificar una columna? Por ejemplo, hemos decidido que para almacenar nombres es mejor usar el campo HUMAN_NAME que NAME. En este caso, podemos aplicar ALTER TABLE

ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;

Si hemos decidido alterar el tipo de un campo, por ejemplo, para aumentar el número de caracteres almacenados en un campo, tendremos que cambiar el dominio de este campo usando la sentencia ALTER DOMAIN (ver el capítulo “Tipos de datos” anterior).

Entonces, hemos considerado la creación y modificación de tablas en InterBase. Ahora es el momento de profundizar un poco en la teoría de bases de datos. InterBase, como ya se ha dicho, es una base de datos relacional. Además, esto significa que cada registro en la tabla debe tener una característica según la cual un registro se puede distinguir de otro. El mecanismo especial de claves únicas sirve para este propósito.

Claves primarias en tablas

Ciertamente, podemos crear una tabla que no contenga ninguna clave. No estamos prohibidos de hacer esto. Pero, como se dijo anteriormente, la creación de una base de datos eficiente es imposible sin observar las reglas de normalización. La presencia de claves es el elemento más importante de la normalización. Por lo tanto, aunque no pretendemos considerar la teoría y la normalización de bases de datos, debemos introducir una definición de claves y revisar su función en InterBase. Iremos paso a paso y comenzaremos con el tipo de clave más común: una clave primaria.

Entonces, ¿qué es una clave primaria? Es uno o más campos en la tabla que identifican de manera única los registros dentro de esta tabla. Suena difícil, sin embargo, en realidad todo es muy simple. Imagine una tabla ordinaria, por ejemplo, la hoja de contabilidad. ¿Cuál es la primera columna? Así es, el número de serie: 1, 2, 3 … Este número denota una línea única dentro de la tabla, y es suficiente conocer este número para encontrar una cadena en esta tabla. En este ejemplo, será una clave primaria. La abrumadora mayoría de las tablas en una base de datos relacional necesariamente tiene una clave primaria (PK - abreviatura de Primary key). La guía común al crear tablas es crear una clave primaria. Una clave primaria se puede crear al crear una tabla o más tarde. Supongamos que en el momento de crear una tabla hemos decidido que el campo ID será nuestra clave primaria. Entonces podemos agregar una clave primaria de la siguiente manera:

CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));

¿Qué se ha hecho para crear una clave primaria para la tabla table_example? Busquemos qué ha variado en la definición de la tabla. Primero, la columna ID ha recibido una definición adicional NOT NULL. Esto es importante, porque la clave primaria debe ser única y no admitir valores indefinidos. Y NULL, como sabes, es un valor indefinido. Por lo tanto, todos los campos incluidos en una clave primaria deben tener la restricción NOT NULL. Para completar la creación de una clave primaria, se debe escribir al final de la tabla: CONSTRAINT ()

Puedes encontrar la sintaxis completa de las restricciones en el capítulo de “Restricciones de base de datos” þ. 1, y para nuestro ejemplo de clave primaria, se verá así:

CONSTRAINT pkTable PRIMARY KEY (ID)

Aquí pkTable es el nombre de la clave primaria, e ID son las columnas que contiene. Esta forma de definir claves primarias para tablas es conveniente cuando se crean tablas en masa (por ejemplo, al construir el prototipo de una base de datos basándose en scripts obtenidos de diferentes recursos CASE). Pero, ¿qué hacer si necesitamos agregar/eliminar una clave primaria a una tabla que ya existe y está llena de datos? Para ello, se debe aplicar otra extensión del comando: ALTER TABLE. Un ejemplo de agregar una clave primaria a nuestra tabla:

ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);

Así, la tabla Table_example tendrá exactamente la misma clave primaria que en el ejemplo anterior cuando se creó junto con la tabla. Para eliminar una clave primaria, se debe ingresar el siguiente comando:

ALTER TABLE Table_example DROP CONSTRAINT pkTable;

De esta manera, la clave con el nombre pkTable se eliminará de la base de datos.

Generadores: mejores amigos de las claves primarias

Debemos decir algunas palabras sobre la implementación de una clave primaria. Como está destinada a mantener la unicidad, no puede haber dos registros en una misma tabla con los mismos valores de esta clave. Es decir, para cumplir la condición, al insertar un nuevo registro en la tabla, InterBase debe verificar todos los registros de la tabla y determinar si contiene tales valores o no. Para una búsqueda rápida, InterBase tiene un mecanismo de índices: objetos especiales de InterBase que permiten encontrar un registro en la tabla muy rápidamente. Por lo tanto, al crear y eliminar la clave primaria, se crea o elimina un índice para ese campo (o campos) que está incluido en la clave primaria.

Como se dijo antes, la clave primaria puede contener varios campos. Así, podemos notar la unicidad de una combinación de valores de estos campos. Por ejemplo, si definimos una clave para los campos ID y NAME, el servidor controlará que no haya combinaciones idénticas de estos campos en la tabla. Es decir, las combinaciones de campos ID y 1 e “Ivanov”, 2 e “Ivanov” serán correctas, ya que difieren en los valores del campo ID.

Por lo tanto, la clave primaria puede incluir varios campos de cualquier tipo. Sin embargo, en la práctica, el tipo de clave más común es el contador: un campo entero que contiene valores crecientes. ¿Por qué es así? Es el reflejo de una vieja disputa entre claves naturales y sustitutas. El concepto de claves naturales dice que como clave debemos intentar usar los valores que realmente existen en el dominio de datos que refleja la base de datos. Por ejemplo, si desarrollamos un sistema de registro de personas para una oficina de pasaportes, según este concepto, una combinación de número y serie del pasaporte debería tomarse como clave primaria. Realmente, cada persona debe tener una combinación única de número y serie del pasaporte. Sin embargo, ¿qué hacer con el hecho de que una persona pueda cambiar su pasaporte durante su vida (debido a alcanzar una edad definida, casarse, etc.)? En este caso, tendremos que cambiar el número y la serie del pasaporte correspondientes a la persona concreta, es decir, en realidad, cambiar nuestra clave primaria. Esto es indeseable desde el punto de vista del desarrollo de aplicaciones de bases de datos: teniendo en cuenta un sistema ramificado de comunicación entre tablas (el siguiente capítulo está dedicado a esto), el desarrollador tendrá que esforzarse mucho para controlar esta situación.

Por lo tanto, en la mayoría de los casos se usa una clave sustituta. Sustituta significa artificial, es decir, que no existe en el dominio de datos que describe nuestra base de datos, y creada artificialmente, para la conveniencia del desarrollo de aplicaciones de bases de datos. Como se dijo, generalmente un contador es la clave primaria. Algunos DBMS, como Paradox y MS SQL, tienen un tipo especial: el contador (auto incremento). Al agregar un nuevo registro a la tabla, un valor de campo aumenta automáticamente con este tipo en el valor de un incremento, generalmente de uno en uno. En InterBase no existe un campo de tipo contador, sin embargo, tal comportamiento se puede realizar. Para crear el campo que se llenaría automáticamente al agregar un registro a la tabla, se utiliza una colección de recursos: el primero de ellos es el generador.

¿Qué es un generador? Hablando de manera simple, un generador es un contador con nombre. Dentro de una base de datos, podemos crear un contador, darle un nombre único dentro de esa base y controlar los valores de este contador. Eso será un generador. Aquí hay un ejemplo de sentencias DDL que te lo explicarán:

CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;

En la primera línea de este ejemplo se crea un generador con nombre g1, y en la segunda línea se asigna el valor 2445 a este generador. Ahora surge la pregunta de cómo usar el generador obtenido. Hay una función incorporada en InterBase llamada GEN_ID para obtener y cambiar los valores de los generadores. Esta función toma como parámetros el nombre del generador y el valor del incremento que se aplicará al generador dado, y devuelve el valor entero correspondiente al valor del generador, recibido como resultado de sumarle el incremento. Aquí hay un ejemplo de llamada a la función GEN_ID en un trigger o procedimiento almacenado:

Current_value = GEN_ID (g1, 1)

Si queremos recibir el valor del generador, podemos usar la siguiente consulta:

SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE

Como la tabla RDB $ Database siempre contiene solo un registro, recibiremos un valor del generador g1 como resultado de la consulta dada.

Aquí current_value es una variable (en los siguientes capítulos encontrarás información sobre cómo usar variables en InterBase), g1 es un generador, 1 es el incremento. En este ejemplo, el valor del generador g1 se asignará a la variable current_value después de sumarle el incremento 1, es decir, el siguiente valor del generador. Presta atención a que el incremento puede no ser igual a 1. Además, puede ser incluso negativo: Current_value = GEN_ID (g1, -23)

Como resultado de ejecutar esta función, el valor actual del generador g1 será menos 23. Como puedes ver, el rango de aplicaciones posibles de los generadores es bastante amplio: se puede usar no solo para obtener valores de claves primarias, sino también para monitorear cambios globales en una base de datos.

Las personas familiarizadas con bases de datos pueden preguntar: “¿qué pasará si simultáneamente varios clientes intentan insertar datos en la misma tabla y simultáneamente ’tiran’ de los generadores? ¿Recibirán los mismos o diferentes valores del generador?”. Recibirán inequívocamente valores DIFERENTES del generador. Por muy “simultáneo” que sea el intento de obtener el valor del generador, todos los que lo soliciten recibirán un valor único. Esto está garantizado por la “construcción” de los generadores: funcionan en el nivel más bajo del servidor y ningún proceso de grabación o inserción los influye; a menudo se dice que los generadores funcionan “fuera del contexto de las transacciones”. Si quieres saber sobre transacciones, lee el capítulo “Transacciones. Parámetros de transacciones” (parte 1); cómo están organizados los generadores: “Estructura de la base de datos InterBase” (parte 4). Bueno, en nombre de los generadores tenemos un mecanismo confiable para crear claves primarias únicas. Sin embargo, ¿podemos usar este mecanismo? ¿Cómo poner el valor recibido del generador en un campo de la clave primaria?

Para este fin, hay dos formas: insertar una clave primaria en nombre del cliente y en nombre del servidor. Para dominar la primera forma, debemos referirnos al capítulo “Uso de los componentes principales de FIBPlus” y para entender la segunda, al capítulo “Triggers” (parte 1). Aquí revisaremos brevemente el punto principal de ambas formas.

En el caso de crear una clave primaria en nombre del cliente, ocurre lo siguiente. Cuando se genera el registro que se insertará en una base de datos, se ejecuta la llamada a la función GEN_ID (, 1) y el valor recibido se sustituye en este registro. Luego se realiza la inserción en la tabla, y tenemos garantizado recibir una clave primaria única.

La segunda forma, crear una clave primaria en nombre del servidor, en general elimina cualquier preocupación por parte del cliente sobre qué valor tendrá la clave primaria. En este caso, al insertar el registro, funciona el trigger: un objeto especial de base de datos que puede realizar cualquier operación al insertar/eliminar/actualizar registros en tablas. Y en este trigger se cumplen las siguientes operaciones: llamada a la función GEN_ID, recepción del valor requerido del generador y su inserción en la tabla. La ventaja de la segunda forma es que al desarrollar una aplicación cliente no hay necesidad de preocuparse por la creación de una clave primaria en absoluto; lo único que tienes que hacer es escribir un trigger necesario una vez. Pero la desventaja es que no podemos recibir el valor de la clave generada en la aplicación justo después de la inserción. Si usamos la primera forma, podemos recibir el valor de la clave primaria, aunque debemos preocuparnos por su creación cada vez que insertamos. Es difícil decir con certeza cuál forma es mejor; todo depende del problema específico. Más adelante en este libro consideraremos posibles variantes para resolver las cuestiones sobre el trabajo con una clave primaria.

Conclusión

Entonces, en este capítulo hemos revisado cómo crear y actualizar tablas en InterBase, así como manejar las claves primarias. Así, hemos considerado los principales objetos en InterBase que pueden llamarse condicionalmente estáticos, porque solo almacenan información y no realizan su conversión. A continuación, hablaremos sobre las formas de control de información y conversión de información dentro de una base de datos.