Constraints, Indices y Modificaciones al diseño de la Tabla

Ya vimos como crear una nueva tabla, lo que vamos a ver ahora es como agregarle a nuestra tabla Constraints y ademas hacerle modificaciones.
Recordemos:

Creación de Tablas Nuevas

CREATE TABLE tabla (
campo1 tipo (tamaño) índice1,
campo2 tipo (tamaño) índice2,... ,
índice multicampo , ... )

En donde:

tabla	Es el nombre de la tabla que se va a crear.
campo1 campo2	Es el nombre del campo o de los campos que se van a crear en la nueva tabla. La nueva tabla debe contener, al menos, un campo.
tipo	Es el tipo de datos de campo en la nueva tabla. (Ver Tipos de Datos)
tamaño	Es el tamaño del campo.
índice1 índice2	Es una cláusula CONSTRAINT que define el tipo de índice a crear. Esta cláusula en opcional.
índice multicampos	Es una cláusula CONSTRAINT que define el tipo de índice multicampos a crear. Un índice multicampo es aquel que está indexado por el contenido de varios campos. Esta cláusula es opcional.

Ahora veamos el siguiente ejemplo.

Ejemplo:
 Vamos a simular una base de datos para un negocio de alquiler de coches, por lo que vamos a empezar creando una tabla para almacenar los coches que tenemos.

CREATE TABLE tCoches
( 
matricula                  char(8)   not null,
marca                      varchar(255)  null,
modelo                     varchar(255)  null,
color                      varchar(255)  null,
numero_kilometros          numeric(14,2) null default 0,
constraint PK_Coches primary key (matricula)
) 

En esta caso se utilizo lo que se llama un constraint esto crea una nueva condición en la tabla se puede crear un costraint al momento de crear la tabla o como veremos mas adelante cuando se modifica.
La sintaxis es:
Para los índices de campos múltiples:

CONSTRAINT nombre {PRIMARY KEY (primario1[, primario2 [,...]]) |
UNIQUE (único1[, único2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [,...]]) REFERENCES tabla externa
[(campo externo1 ,campo externo2 [,...])]}

En donde:

nombre	Es el nombre del índice que se va a crear.
primarioN	Es el nombre del campo o de los campos que forman el índice primario.
únicoN	Es el nombre del campo o de los campos que forman el índice de clave única.
refN	Es el nombre del campo o de los campos que forman el índice externo (hacen referencia a campos de otra tabla).
tabla externa	Es el nombre de la tabla que contiene el campo o los campos referenciados en refN
campos externos	Es el nombre del campo o de los campos de la tabla externa especificados por ref1, ref2,... , refN

También:

Indice	Descripción
UNIQUE	Indica que los valores de la columna en referencia deben ser únicos.
PRIMARY KEY	Genera un índice primario el campo o los campos especificados. Todos los campos de la clave principal deben ser únicos y no nulos, cada tabla sólo puede contener una única clave principal.
FOREIGN KEY	Genera un índice externo (toma como valor del índice campos contenidos en otras tablas). Si la clave principal de la tabla externa consta de más de un campo, se debe utilizar una definición de índice de múltiples campos, listando todos los campos de referencia, el nombre de la tabla externa, y los nombres de los campos referenciados en la tabla externa en el mismo orden que los campos de referencia listados. Si los campos referenciados son la clave principal de la tabla externa, no tiene que especificar los campos referenciados, predeterminado por valor, el motor Jet se comporta como si la clave principal de la tabla externa estuviera formada por los campos referenciados.

En ocasiones puede ser necesario modificar la estructura de una tabla, comúnmente para añadir un campo o restricción. Para ello disponemos de la instruccción ALTER TABLE.


ALTER TABLE nos va a permitir: 

• Añadir campos a la estructura inicial de una tabla. 
• Añadir restricciones y referencias. 

Para añadir un campo a una tabla existente, Esta es la sintaxis:


ALTER TABLE <nombre_tabla>
ADD <nombre_campo> <tipo_datos(tamaño)>
[null |not null] [default <valor_por_defecto>]
{

, <nombre_campo> <tipo_datos(tamaño)>
[null |not null] [default <valor_por_defecto>]}

Por Ejemplo para nuestra tabla coches:

ALTER TABLE tCoches
ADD num_plazas integer null default 5

En este ejemplo añadimos el campo num_plazas a la tabla tCoches.

Para añadir una clave primaria vamos a crear una tabla de cliente y le añadiremos la clave primaria ejecutando una sentencia alter table:


CREATE TABLE tClientes

 ( codigo      integer            not null,

   nombre     varchar(255)  not null,

   apellidos   varchar(255)  null, 

   nif             varchar(10)    null, 

   telefono    varchar(9)       null, 

   movil        varchar(9)       null

 )

ALTER TABLE tClientes 

ADD CONSTRAINT PK_tClientes  primary key (codigo)


Creamos la tabla clientes y le añadimos una reestricción primary key a la que damos el nombre PK_tClientes en el campo codigo.

Solo podemos modificar una única tabla a la vez con ALTER TABLE, para modificar más de una tabla debemos ejecutar una sentencia ALTER TABLE por tabla.

Para añadir una clave externa (o foranea) necesitamos una tercera tabla en nuestra estructura. Por un lado tenemos la tabla tCoches y la tabla tClientes, ahora vamos a crear la tabla tAlquileres que será la encargada de "decirnos" que clientes han alquilado un coche.

CREATE TABLE tAlquileres 

( codigo                integer   not null,

  codigo_cliente    integer    not null,

  matricula            char(8)   not null,

  fx_alquiler          datetime not null, 

  fx_devolucion    datetime null 

)

ALTER TABLE tAlquileres 

ADD CONSTRAINT PK_tAlquileres primary key (codigo), 

         CONSTRAINT FK_Clientes foreign key (codigo_cliente) references tClientes (Codigo),

         CONSTRAINT FK_Coches foreign key (matricula) references tCoches (matricula); 

Bien, en este código creamos la tabla tAlquileres, y luego mediante una sentencia ALTER TABLE añadimos una clave primaria llamada PK_tAlquileres en el campo codigo, una clave externa llamada FK_Clientes referenciada al codigo de la tabla tClientes, y por último otra clave externa llamada FK_Coches referenciada al campo matricula de la tabla tCoches.

Nota:Cuando creamos una clave externa el campo referenciado y el que sirve de referencia deben ser del mismo tipo de datos. Si somos observadores nos daremos cuenta que los campos que sirven de referencia a las claves foraneas son las claves primarias de sus tablas. Sólo podemos crear claves externas que referencien claves primarias.

Al igual que ocurria con la sentencia CREATE TABLE cada gestor de bases de datos implementa sus mejoras, siendo la mejor forma de conocerlas recurrir a la documentación del gestor de bases de datos. 

Podemos eliminar los constraint y los PK y FK si utilizamos la sentencia DROP.

Por Ejemplo:

ALTER TABLE
Pedidos
DROP CONSTRAINT RelacionPedidos
(Elimina el índice de la tabla Pedidos.)

O tambien podemos agregar columnas y eliminarlas

ALTER TABLE
Empleados
ADD COLUMN Salario numeric(5,2)
(Agrega un campo Salario de tipo Moneda a la tabla Empleados.)

ALTER TABLE
Empleados
DROP COLUMN Salario
(Elimina el campo Salario de la tabla Empleados.)

Eliminación de tablas.


Podemos eliminar una tabla de una base de datos mediante la instruccion DROP TABLE.
La sintaxis es así:

DROP TABLE <nombre_tabla>;


La instrucción DROP TABLE elimina de forma permanente la tabla y los datos en ella contenida.

Si intentamos eliminar una tabla que tenga registros relacionados a través de una clave externa la instrucción DROP TABLE fallará por integridad referencial. Es decir por restricciones en sus relaciones porque cuando eliminamos una tabla eliminamos también sus índices.