Capítulo IV. Cómo crear y eliminar los índices de las tablas

Publicado el 02/04/2004 por Enrique Martínez Montejo. Enlace original

Contenido

• Introducción
• La cláusula COINSTRAINT y la integridad referencial
  • Tabla principal y tabla externa
  • La integridad referencial
• Cómo crear un índice
  • Con la instrucción CREATE TABLE
  • Con la instrucción ALTER TABLE
  • Con la instrucción CREATE … INDEX
• Cómo eliminar un índice
  • Con la instrucción ALTER TABLE
  • Con la instrucción DROP INDEX

Introducción

Podemos definir el término índice como el recurso necesario para acelerar la búsqueda y ordenación de los datos incluidos en una tabla, utilizando para ello valores clave que pueden exigir que las filas de una tabla sean únicas. No es necesario crear índices en una tabla, aunque sí es recomendable utilizarlos para aquellos conjuntos de datos que tengan un volumen considerable de registros, ya que se agilizará el tiempo de acceso a un determinado registro como también el proceso de combinación de registros entre dos o más tablas.

Si bien es recomendable que los registros de una tabla se puedan indexar, el crear demasiados índices tiene un efecto negativo, ya que al actualizar los datos el motor Microsoft Jet tiene que mantener automáticamente todos los índices de la tabla base, actualizando estos cada vez que se añaden, modifican o eliminan registros de la tabla, por lo que la actualización de la base de datos puede demorar un tiempo bastante considerable. Es una buena práctica compactar periódicamente la base de datos a fin de que se puedan actualizar las estadísticas del índice.

Los índices determinan el orden en el que aparecerán los registros devueltos por una consulta de selección, aunque se pueden ordenar por otro campo distinto del campo que forma el índice, pero en ningún caso determinan el orden en el que se almacenarán los datos en la tabla base.

La cláusula CONSTRAINT y la integridad referencial

Una restricción CONSTRAINT es simplemente un índice, aunque también podemos utilizar dicha cláusula para establecer una relación con otra tabla, entendiendo por relación la asociación establecida entre campos comunes de dos tablas. Dicha cláusula sólo se puede especificar con las instrucciones CREATE TABLE y ALTER TABLE, si nuestra intención es crear un índice o una relación, y únicamente con ALTER TABLE si deseamos eliminar cualquier índice o relación existente.

La cláusula CONSTRAINT siempre irá al principio de la definición del índice, pudiéndose crear un índice sobre un campo individual o sobre un grupo de campos, utilizando para ello la siguiente sintaxis dependiendo del número de campos que formen el índice.

• Para los índices de campos únicos:

CONSTRAINT nombre {PRIMARY KEY | UNIQUE | NOT NULL |
    REFERENCES tablaExterna [(campoExterno1 [, campoExterno2])]
    [ON UPDATE CASCADE | SET NULL]
    [ON DELETE CASCADE | SET NULL]}

El siguiente ejemplo creará un índice de un único campo que a su vez será la clave principal de la tabla:

CREATE TABLE Facturas (IdFactura INTEGER CONSTRAINT ClavePrincipal PRIMARY KEY)

• Para los índices de campos múltiples:

CONSTRAINT nombre
    {PRIMARY KEY (principal1[, principaI2[, ...]]) |
    UNIQUE (único1[, único2[, ...]]) |
    FOREIGN KEY [NO INDEX] (ref1[,ref2[, ...]]) REFERENCES tablaExterna [(campoExterno1 [,campoExterno2 [, ...]])]
    [ON UPDATE CASCADE | SET NULL]
    [ON DELETE CASCADE | SET NULL]}

A continuación crearemos un índice de varios campos el cual no podrá contener valores duplicados:

ALTER TABLE Clientes ADD CONSTRAINT codigoFiscal UNIQUE (Nombre, CIF)

A continuación se detallan los parámetros utilizados en ambas versiones.

Argumento	Descripción
nombre	El nombre del índice o restricción que se desea crear.
principal1, principal2	El nombre del campo o de los campos que han sido designados para formar la clave principal.
único1, único2	El nombre del campo o de los campos que han sido designados para formar una clave única.
ref1, ref2	El nombre de un campo o de los campos de la clave externa a los que se hace referencia en campos de otra tabla.
tablaExterna	El nombre de la tabla externa que contiene el campo o los campos especificados por campoExterno.
campoExterno1, campoExterno2	El nombre del campo o campos de tablaExterna especificados por ref1, ref2. Se puede omitir esta cláusula si el campo al que se hace referencia es la clave principal de la tabla externa.

Son tres los tipos de índices que se pueden crear mediante la cláusula CONSTRAINT:

• UNIQUE: genera un índice como clave única formado por un campo o un conjunto de campos de la tabla. Esto implica que los registros de la tabla no pueden tener el mismo valor en los campos indexados. Si el índice de clave única está formado por varios campos, los valores combinados de todos los campos del índice deben ser únicos, aunque dos o más registros de la tabla tengan el mismo valor en uno de los campos que conforman el índice de múltiples campos.
• PRIMARY KEY: genera un índice como clave principal formado por un campo o un conjunto de campos de la tabla. Todos los valores de los campos que conforman el índice primario deben ser únicos y no nulos. Cada tabla sólo puede tener una única clave principal, produciéndose un error interceptable si se intenta crear una nueva clave principal en una tabla donde ya existe una restricción PRIMARY KEY.
• FOREIGN KEY: genera un índice como clave externa, lo que significa que tomará como valor del índice un campo o conjunto de campos contenidos en otras tablas. Si la clave principal de la tabla externa está formado por más de un campo, debe utilizar una definición de índice de múltiples campos, enumerando todos los campos de referencia, el nombre de la tabla externa y los nombres de los campos referenciados en la tabla externa y en el mismo orden en que se especificaron los campos de referencia anteriormente enumerados. Si el campo o los campos referenciados son la clave principal de la tabla externa, no tiene que especificar los campos referenciados de manera predeterminada, ya que el motor de datos Microsoft Jet se comportará como si la clave principal de la tabla externa fuera el campo referido.

Por ejemplo, si deseamos añadir el índice Clientes_Facturas a la tabla Facturas de  nuestra base de datos, tendríamos que ejecutar la siguiente consulta SQL:

ALTER TABLE Facturas ADD CONSTRAINT Clientes_Facturas FOREIGN KEY (NumCliente) REFERENCES Clientes (IdCliente)

Al utilizar la palabra clave FOREIGN KEY para crear un índice, estaremos creando una relación llamada Clientes_Facturas entre el campo que conforma la clave principal de la tabla Clientes y el campo NumCliente externo de la tabla Facturas, por lo que el motor Jet exigirá que se cumpla la integridad referencial de los datos.

Tabla principal y tabla externa

Antes de hablar sobre la integridad referencial, conviene definir ciertos conceptos que le ayudarán a entender los elementos que conforman una relación entre dos tablas.

Una tabla principal es el lado uno de dos tablas relacionadas mediante una relación de uno a varios, y debe de tener una clave principal donde cada registro debe ser único. Un ejemplo de una tabla principal sería el de la típica tabla de Clientes, donde cada registro se identifica de manera única mediante el campo IdCliente, el cual forma parte a su vez de la clave principal de la tabla.

Generalmente, una tabla externa se corresponde con el lado varios de una relación de uno a varios, la cuál contará con un campo de clave externa, que estará referenciando a la clave principal existente en otra tabla de la base de datos (el lado uno de la relación). Un ejemplo de tabla externa es una tabla de Facturas de clientes, cuyo campo NumCliente está referenciando a una clave externa existente en la tabla de Clientes, cuya clave principal estará formada por el campo IdCliente.

La clave principal de una tabla deberá estar formada por uno o varios campos cuyo valor o valores identifican de manera única cada registro de la tabla. Una clave principal no puede permitir valores NULL (Nulos) y debe tener siempre un índice único. La clave principal se utiliza para relacionar una tabla con claves externas de otras tablas.

La clave externa estará formada también por uno o varios campos de una tabla, los cuales hacen referencia al campo o campos que conforman la clave principal de otra tabla de la base de datos. Una clave externa indica cómo deben estar relacionadas las tablas, y los tipos de datos de los campos de ambas claves (externa y principal) deben coincidir, aunque los nombres de los campos sean distintos, por lo que si la clave principal tiene un tipo de datos Integer, la clave externa debe tener el mismo tipo de dato. Por ejemplo, una tabla de Facturas puede contener una clave externa llamada NumCliente (que no tiene por qué denominarse igual que el nombre de un campo existente en la misma tabla), que hace referencia a la clave principal llamada PrimaryKey formada por el campo único IdCliente de una hipotética tabla de Clientes.

	CLIENTES		FACTURAS
	(Tabla principal)		(Tabla externa)
Clave principal de la tabla, denominada PrimaryKey	IdCliente		IdFactura IdCliente	Clave externa de la tabla, denominada NumCliente

La integridad referencial

Podemos definir el término integridad referencial como el conjunto de reglas que se siguen para preservar las relaciones definidas entre las tablas a la hora de añadir, modificar o eliminar registros.

Como se ha indicado anteriormente, mediante la cláusula CONSTRAINT podemos definir claves principales y externas para crear relaciones entre tablas de nuestra base de datos Access, y hacer que se cumpla las reglas de la integridad referencial, las cuales protegen las relaciones entre las tablas cuando se añaden, modifican o eliminan registros. De ésta forma se impide a los usuarios que añadan registros a una tabla relacionada para la que no existe un registro asociado en la tabla principal (no podemos asignar una factura a un identificador de cliente no existente), modificar los valores de la tabla principal que podrían dar lugar a registros huérfanos en la tabla relacionada, salvo que en la relación se haya definido la actualización de datos en cascada, así como eliminar los registros de la tabla principal cuando haya registros relacionados coincidentes en la tabla relacionada, salvo que como en el caso anterior, se permita la eliminación de registros en cascada en la definición de la relación creada.

Cómo crear un índice

Existen tres formas de crear un índice mediante las instrucciones del Lenguaje de Definición de Datos de SQL que proporciona el motor de base de datos Microsoft Jet:

• Al crear la tabla, con la instrucción CREATE TABLE.
• Con la instrucción ALTER TABLE.
• Con la instrucción CREATE INDEX.

Con cualquiera de las tres formas se puede crear un índice, aunque existen diferencias entre ellas. Mediante las instrucciones CREATE TABLE o ALTER TABLE, podemos añadir una clave externa para exigir la integridad referencial de los datos, debiendo utilizar para ello una cláusula CONSTRAINT. Esta cláusula no es necesario especificarla si utilizamos la instrucción CREATE INDEX, aunque no podremos entonces exigir la integridad referencial.

No es necesario crear el índice mediante la instrucción CREATE TABLE, por lo que primero podemos crear la tabla con dicha instrucción, y posteriormente agregar los índices a la tabla mediante la instrucción ALTER TABLE o CREATE INDEX.

Hacer la salvedad que dichas instrucciones solo se pueden utilizar para crear índices en bases de datos Microsoft Access, que son las bases de datos nativas del motor Microsoft Jet.

Con la instrucción CREATE TABLE

Puede utilizar la sintaxis de una restricción (CONSTRAINT) de un solo campo en la cláusula de definición de campo de una instrucción CREATE TABLE, definiéndola inmediatamente después de la especificación del tipo de datos del campo.

Utilice la sintaxis de restricción de múltiples campos siempre que utilice la palabra reservada CONSTRAINT fuera de la cláusula de definición de campo en una instrucción CREATE TABLE.

El siguiente ejemplo creará un índice de un único campo que a su vez será la clave principal de la tabla:

CREATE TABLE Clientes (
    IdCliente INTEGER CONSTRAINT CIavePrincipal PRIMARY KEY,
    Nombre TEXT (50) WITH COMP NOT NULL)

A continuación crearemos un índice de varios campos el cual no podrá contener valores duplicados:

CREATE TABLE Clientes (
    Nombre TEXT (50) WITH COMP,
    IdCliente INTEGER,
    CONSTRAINT IndiceClientes UNIQUE (Nombre, IdCliente))

La diferencia existente entre crear un índice de un único campo y un índice de varios campos está en que, para el índice de un sólo campo, la cláusula CONSTRAINT debe aparecer inmediatamente después de la especificación del campo indexado, y para el índice de varios campos dicha cláusula debe aparecer fuera de la instrucción CREATE TABLE, estando separada de ésta por una coma.

Con la instrucción ALTER TABLE

Si la tabla ya existe, también podemos añadir un índice utilizando la cláusula CONSTRAINT con la instrucción ALTER TABLE, la cuál utiliza la siguiente sintaxis:

ALTER TABLE tabla ADD CONSTRAINT índice
{PRIMARY KEY(principal1[,principal2[,...]]) |
UNIQUE (único1[,único2[,...]]) |
FOREIGN KEY (ref1[,ref2[,...]]) REFERENCES tablaExterna[(campoExterno1[,campoExterno2[,. ..]])]}

El siguiente ejemplo creará la clave principal de una tabla ya existente:

ALTER TABLE Empleados ADD CONSTRAINT ClavePrincipal PRIMARY KEY (IdEmpleado)

A continuación crearemos un índice único formado por múltiples campos de una tabla existente:

ALTER TABLE Alumnos ADD CONSTRAINT NombreCompleto UNIQUE (Nombre, Apellidos)

Por último creamos una relación de uno a varios entre la tabla Facturas (tabla principal) y Albaranes (tabla externa):

ALTER TABLE Albaranes
    ADD CONSTRAINT NumAlbaranes
    FOREIGN KEY (NumFactura) REFERENCES Facturas (IdFactura)
    ON UPDATE CASCADE
    ON DELETE CASCADE

Con la instrucción CREATE…INDEX

Una vez que la tabla ya exista en nuestra base de datos, podemos crear un índice utilizando la siguiente sintaxis:

CREATE [UNIQUE] INDEX índice
    ON tabla (campo [ASC|DESC][, campo [ASC|DESC], ...])
    [WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]

En el momento de crear el índice podemos especificar que se cumplan ciertas reglas de validación de datos mediante la cláusula opcional WITH, la cual puede incluir las siguientes palabras clave:

• PRIMARY, significa que el campo es la clave principal de la tabla.
• DISALLOW NULL, indica que todos los campos que conforman el índice deben rellenarse, por lo que el campo no se puede dejar en blanco.
• IGNORE NULL, si se especifica, indica que el registro no se indexará si el campo está en blanco.

Las tres cláusulas se pueden especificar a la misma vez (WITH PRIMARY DISALLOW NULL IGNORE NULL), pero debe saber que si se especifica PRIMARY, ya se incluye en el índice el valor UNIQUE y DISALLOW NULL, debido a que los valores que conforman la clave principal será únicos y no podrán dejarse en blanco. En cuanto a las cláusulas DISALLOW NULL e IGNORE NULL, debe tener en cuenta lo siguiente:

SI IGNORE NULL es	y DISALLOW NULL es	Entonces
True	False	Valor NULL permitido; no se añade ninguna entrada al índice.
False	False	Valor NULL permitido; se añade una entrada al índice.
True o False	True	Valor NULL no permitido; no se añade ninguna entrada al índice.

Si no se especifica la cláusula WITH se creará un índice donde se admiten entradas duplicadas.

El siguiente ejemplo creará un índice donde se permitirá entradas duplicadas en el campo IdCliente:

CREATE INDEX Mi_Indice ON Clientes (IdCliente)

A continuación, añadimos un índice con la cláusula WITH donde no se podrá añadir a la tabla ningún registro cuyo campo IdCliente esté vacío:

CREATE UNIQUE INDEX Mi_Indice ON Clientes (IdCliente) WITH DISALLOW NULL

Cómo eliminar un índice

Una vez creada la tabla y sus correspondientes índices, puede que nos resulte necesario eliminar algún índice, bien de la tabla o de una relación existente, por lo que el lenguaje SQL del motor Microsoft Jet pone a nuestra disposición dos instrucciones para llevar a cabo tal cometido. Pero antes de estudiar por separado la sintaxis de cada una de ellas, conviene indicar las observaciones a tener en cuenta cuando vaya a ejecutar una consulta de eliminación de índice:

• Con ninguna de las dos instrucciones se puede eliminar más de un índice a la vez.
• Debe de cerrar la tabla antes de proceder a la eliminación de un índice. Si es una relación existente la que desea eliminar, procure que no se encuentre abierta ninguna de las tablas que conforma la relación.
• Si desea eliminar la clave principal de una tabla que forma el lado uno de una relación de uno a varios, deberá primero eliminar la relación existente, dado que no le estará permitido eliminar el índice activo que forme parte de una relación, tanto si ésta es de uno a varios como si la relación lo es de uno a uno.
• Si desea eliminar una relación, únicamente lo podrá hacer con la instrucción ALTER TABLE.

Con la instrucción ALTER TABLE

Para eliminar un índice con la instrucción ALTER TABLE, utilice la siguiente sintaxis:

ALTER TABLE nombreTabla DROP CONSTRAINT nombreÍndice

Seguidamente voy a exponer un ejemplo para eliminar la clave principal de la tabla Facturas. Pero recuerde que dicha clave es el índice activo (el lado uno) de la relación NumAlbaranes creada en un ejemplo anterior, por lo que previamente tendremos que eliminar la relación para poder eliminar la clave principal de la tabla:

' Eliminamos la relación existente
ALTER TABLE Albaranes DROP CONSTRAINT NumAlbaranes

' Eliminamos la clave principal
ALTER TABLE Clientes DROP CONSTRAINT ClavePrincipal

Eliminar un índice con la instrucción DROP INDEX

Recuerde que esta instrucción no permite eliminar una relación existente. Si se decide por ejecutar la instrucción DROP INDEX para llevar a efecto la eliminación de un índice, utilice esta sintaxis:

DROP INDEX nombreÍndice ON nombreTabla

A continuación eliminaremos el índice multicampos creado anteriormente:

ALTER TABLE Alumnos DROP CONSTRAINT NombreCompleto

El siguiente ejemplo utilizará ADO .NET para establecer una conexión con una base de datos Microsoft Access y ejecutar una consulta SQL, que en éste caso servirá para eliminar la relación de uno a varios existente entre la tabla Facturas (tabla principal) y Albaranes (tabla externa), así como las claves principales de dichas tablas. La misma sintaxis SQL se puede utilizar también con el método Execute del objeto Connection (ADO) y del objeto Database (DAO):

' Importamos el siguiente espacio de nombre
'
Imports System.Data.OleDb

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Try
        ' Configuramos y abrimos la conexión.
        '
        Using cnn As New OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Mis documentos\Bd1.mdb")

            cnn.Open()

            Dim cmd As OleDbCommand = cnn.CreateCommand()

            ' Creo la cadena SQL para eliminar la relación.
            '
            cmd.CommandText = "ALTER TABLE Albaranes DROP CONSTRAINT FacturasAlbaranes"

            ' Ejecutamos el comando.
            '
            cmd.ExecuteNonQuery()

            MessageBox.Show("Se ha eliminado la relación.", _
                            "ALTER TABLE", MessageBoxButtons.OK, _
                            MessageBoxIcon.Information)

            ' Creo la cadena SQL para eliminar la clave principal.
            '
            cmd.CommandText = "ALTER TABLE Albaranes DROP CONSTRAINT ClavePrincipal"
            cmd.ExecuteNonQuery()

            MessageBox.Show("Se ha eliminado la clave principal de la tabla Albaranes.", _
                            "ALTER TABLE", MessageBoxButtons.OK, _
                            MessageBoxIcon.Information)

            ' Creo la cadena SQL para eliminar la segunda clave principal.
            '
            cmd.CommandText = "DROP INDEX ClavePrincipal ON FACTURAS"
            cmd.ExecuteNonQuery()

            MessageBox.Show("Se ha eliminado la clave principal de la tabla Facturas", _
                            "DROP INDEX", MessageBoxButtons.OK, _
                            MessageBoxIcon.Information)
        End Using

    Catch ex As Exception
        MessageBox.Show(ex.Message)

    End Try

End Sub