Tema 8: Crear y administrar tablas.

Una vez que se ha creado la base de datos e identificado el tipo de datos, se está listo para crear los objetos tabla que almacenaran los datos dentro de la base de datos. Cuando se crea una tabla, la definición de la tabla deberá incluir, como mínimo, el nombre de la tabla, los nombres de las columnas, sus tipos de datos (y longitudes, en caso de ser necesarios) y si las columnas aceptarán valores NULL. Se pueden configurar otras propiedades posteriormente, aunque es aconsejable definir la mayor cantidad de propiedades al crear la tabla para evitar problemas posteriores de mantenimiento. En este punto trataremos como crear tablas, incluyendo como especificar la anulabilidad, generar valores para las columnas y definir valores por defecto en las columnas. Además, se verá como obtener información acerca del objeto tabla, modificar las características de las tablas,  y eliminar tablas.

Crear tablas en una base de datos SQL-Server

Una tabla es una colección de datos acerca de una entidad específica, como un cliente, una orden de  compra, o un inventario. Una tabla contiene un conjunto de columnas. Cada columna representa un atributo de la entidad. Por ejemplo, la fecha de una orden podría ser un atributo de la entidad orden. Cada instancia de la entidad en la tabla es representada por un solo registro o fila (llamada también tupla).

En este punto del desarrollo de la base de datos se deberá contar con la información necesaria para crear las tablas en la base de datos. Idealmente, se debería contar con toda la información acerca de las tablas, incluyendo tanto las restricciones de clave primaria PRIMARY KEY como todas las otras restricciones. Primero aprenderá a crear tablas básicas y posteriormente se verá como incorporar las restricciones

Determinar la anulabilidad de las columnas

La anulabilidad de una columna determina si las filas en la tabla pueden contener un valor nulo para esta columna. Un valor nulo no es lo mismo que un cero, un blanco o una cadena de caracteres de longitud cero. Nulo significa que ninguna entrada ha sido cargada para ese dato. La presencia de un valor nulo generalmente implica que el valor o es desconocido o no esta definido. Por ejemplo, un valor nulo en la columna Precio de la tabla Libros de la base Pubs no significa que no tenga precio, sino que este es desconocido o no ha sido ingresado.

En general, se debe evitar permitir ingresar valores nulos porque ello provoca mayor complejidad en las consultas y actualizaciones y porque estos no pueden ser usados con algunas opciones de configuración de las columnas, como la restricción PRIMARY KEY. Comparaciones entre dos valores nulos, o entre un valor nulo y cualquier otro valor, retornan un valor desconocido (unknow) porque el valor de cada NULL es desconocido por definición. Valores nulos no pueden ser usados para información requerida para distinguir una fila de otra fila en una tabla. Además, eliminar valores nulos cuando se están realizando operaciones de cálculo puede ser importante porque ciertos cálculos (tales como promedios) pueden ser inexactos si existe valores nulos dentro de los datos analizados. Si se necesita crear columnas donde algunos de cuyos valores serán desconocidos se puede crear un valor por defecto que sea asignado en tales casos.

Por ejemplo, la columna Telefono en la tabla Autores de la base de datos Pubs no permite valores nulos. La columna incluye un valor por defecto DESCONOCIDO para cuando no se tiene el dato. Si el valor para la columna Telefono no es agregado en el comando de grabación del registro, el SQL automáticamente grabará DESCON en dicha columna. Mas adelante se verá como definir valores por defecto para las columnas.

Si una fila es insertada pero sin valores para una columna que permite valores nulos, SQL Server  provee un valor nulo (sino existe un definición por defecto).

Una columna definida con la cláusula NULL también acepta una entrada explícita de NULL por parte del usuario, sin importar cual sea el tipo de dato de la columna o si tiene un valor por defecto definido. El valor NULL no debe ser expresado entre comillas porque será interpretado como una cadena de caracteres en vez de un valor NULL.

Especificar una columna para que no permita valores nulos puede ayudar a mantener la integridad de los datos asegurando que la columna tiene datos para todas las filas. Si los valores nulos no son permitidos, el usuario que ingresa los datos se verá obligado a ingresar datos en la columna o toda la fila será rechazada por el sistema.

Columnas definidas con las cláusulas PRIMARY KEY o IDENTITY no pueden permitir valores nulos.

La anulabilidad de una columna se define cuando se define la columna, ya sea cuando se crea o modifica una tabla. Para definir columnas que permiten o no valores se utilizan las cláusulas NULL o NOT NULL respectivamente.

El siguiente ejemplo usa el comando CREATE TABLE para crear la tabla Empleados. La columna Emp_ID y la columna Apel no permiten valores nulos, mientras que la columna Nombre sí.

CREATE TABLE Empleados
(
Emp_ID char(4) NOT NULL,
Nombre varchar(30) NULL,
Apel varchar(30) NOT NULL,
)

Definir valores por defecto

Cada columna en un registro debe contener un valor (aún si ese valor es un valor nulo). Hay situaciones en la cuales se necesita cargar una fila de datos en una tabla donde no se conocen los valores para todas las columnas ( o esos valores no existen aún). Si la columna permite valores nulos, se puede cargar un valor nulo para esa fila. Dado que como vimos los valores nulos no son aconsejables, una mejor solución puede ser definir una valor por defecto para esa columna,. Por ejemplo, es común asignar un valor cero como el valor por defecto (DEFAULT) para columnas numéricas o N/A para columnas de caracteres cuando no se especifican valores.

La cláusula DEFAULT en el comando CREATE TABLE se considera una restricción aún cuando en realidad no fuerza a nada.

Cuando se carga una fila en una tabla con una definición de un valor por defecto para una columna, se le está indicando en forma implícita al SQL Server que cargue el valor por defecto en la columna en aquellos casos que no se indique valor para dicha columna.

Si una columna no permite valores nulos y no tiene una definición por defecto, se deberá explícita indicar un valor para esa columna o el SQL Server generará un mensaje de error indicando que la columna no permite valores nulos.

Se puede crear una definición de valores por defecto para una columna de dos maneras:

·        Creando la definición del valor por defecto cuando se crea la tabla (como parte de la definición de la tabla)

·        Agregando el valor por defecto a una tabla existente (cada columna permite solo un valor por defecto)

El siguiente ejemplo usa el comando CREATE TABLE para crear la tabla Empleados. Ninguna de las tres columnas permite valores nulos; sin embargo, la columna Nombre provee la posibilidad de un nombre desconocido al agregar una definición por defecto a la definición de la columna. El comando CREATE TABLE usa la cláusula DEFAULT para definir el valor por defecto:

CREATE TABLE Empleados
(
Emp_ID char(4) NOT NULL,
Nombre varchar(30) NOT NULL DEFAULT ‘desconocido’,
Apel varchar(30) NOT NULL,
)

Se puede modificar o eliminar una definición por defecto existente. Por ejemplo se puede modificar el valor insertado en una columna cuando no ha sido entrado ningún valor.

Cuando se utilice el Transact-SQL para modificar una definición por defecto, se debe primero borrar la cláusula DEFAULT existente y luego recrearla con la nueva definición.

La definiciones DEFAULT no pueden ser creadas sobre columnas creadas con alguna de las siguientes características:

·        Con tipo de dato timestamp

·        Con la propiedad IDENTITY o ROWGUIDCOL

·        Con una definición por defecto existente o con un objeto por defecto.

El valor por defecto debe ser compatible con el tipo de dato definido para la columna. Por ejemplo para una columna con un tipo de dato entero (int) el valor por defecto deberá ser un número entero y no una cadena de caracteres.

Cuando una definición DEFAULT es agregada a una columna existente en una tabla, SQL Server (por defecto) aplica el valor por defecto solo a las filas nuevas que sean ingresadas de ahí en adelante. Las filas que tomaron el valor por defecto anterior no son afectadas. Cuando se agrega una nueva columna a una tabla existente, sin embargo, se puede especificar al SQL Server que inserte el valor por defecto (especificado en la definición de la nueva columna) en vez de un valor nulo en la nueva columna  para las filas preexistentes de la tabla.

Auto numeración y columnas de identificación

Para cada tabla, se puede crear una sola columna de identificación conteniendo los valores secuenciales generados por el sistema que unívocamente identifican cada fila en la tabla. Por ejemplo, una columna de identificación podría generar automáticamente un número único de recibo de compra a medida que las filas son insertadas en la tabla.

Las columnas de identificación contienen valores únicos dentro de la tabla en la cual ellas son definidas. En otras palabras, otras tablas que contienen columnas de identificación pueden contener los mismos valores de identificación que los usados por otras tablas. Sin embargo, esta situación generalmente no es un problema, porque los valores de identificación son típicamente usados solamente dentro del contexto de una sola tabla, y las columnas de identificación no se relacionan a otra columna de identificación de otra tabla.

Puede ser creada una columna de identificación globalmente única para cada tabla que deba contener valores que son únicos para todas las redes de computadoras del mundo. A menudo, se utiliza una columna que garantice contener valores globalmente únicos  cuando datos similares pueden ser generados desde múltiples sistemas de base de datos. (por ejemplo en un sistema de facturación a clientes con datos ubicados en varias subsidiarias distribuidas por el mundo). Cuando el dato es tomado en el sitio central para consolidación y para la generación de reportes, el utilizar datos globalmente únicos permite a los clientes en diferentes países evitar tener los mismos números de factura o de identificación de cliente. SQL Server utiliza internamente columnas de identificación únicas globalmente en procesos de replicación, asegurando que las filas son identificadas de forma unívoca a través de las múltiples copias de la tabla.

Crear columnas de identificación

Solamente una columna de identificación y una columna de identificación globalmente única pueden crearse por cada tabla

Propiedad IDENTITY

Las columnas de identificación pueden ser implementadas usando la propiedad IDENTITY, la cual especifica el primer número de identificación a ser usado en la primera fila que se ingrese y un incremento (propiedad Identity Increment) que se agregará al último usado para generar un nuevo número de identificación para una fila cualquiera que sea agregada. Cuando se insertan valores en una tabla con una columna de identificación, SQL Server automáticamente genera el próximo número de identificación adicionando el incremento al último generado.

Cuando se usa la propiedad IDENTITY para definir una columna de identificación, se debe considerar lo siguiente:

·        Una tabla puede tener solamente una columna con la propiedad IDENTITY property, y la columna debe ser definida utilizando los tipos de datos int, numeric, smallint, bigint, o tynint.

·        Se pueden especificar el número inicial y el incremento de la secuencia, siendo ambos por defecto iguales a uno.

·        La columna de identificación no debe permitir valores nulos y no debe contener una definición u objeto por defecto.

·        La columna puede ser referenciada en una lista de selección utilizando la palabra clave IDENTIFYCOL después que la propiedad IDENTITY ha sido configurada.

·        La función OBJECTPROPERTY puede ser usada para determinar si una tabla tiene una columna IDENTITY, y la función COLUMNPROPERTY puede ser usada para determinar el nombre de la columna IDENTITY.

El siguiente ejemplo utiliza el comando CREATE TABLE para crear la tabla Empleados. Ninguna columna acepta valores nulos. Además, la columna Emp_ID es una columna de identificación. El valor inicial es 101, y el incremento 1.

CREATE TABLE Empleados
(
Emp_ID SMALLINT IDENTITY(101,1) NOT NUL,
EmpNombre varchar(50) NOT NULL,
)

Si existe una columna de identificación en una tabla sobre la que se realizan frecuentes  operaciones de borrado de filas, se puede generar huecos en la secuencia de valores de la columna. Los valores eliminados de las columnas de identificación no será reutilizados. Si se quieren evitar tales huecos no se debería utilizar la propiedad IDENTITY, en cambio, se puede crear un desencadenador que determine un nuevo valor de identificación (basado en los valores que ya existen en la columna de identificación) para cada nueva fila.

Identificadores globalmente únicos

Aunque la propiedad IDENTITY automatiza la numeración de las filas dentro de una tabla; tablas separadas, sin embargo, cada una con su propia columna de identificación, pueden generar los mismos valores para sus columnas de identificación. La propiedad IDENTITY garantiza valores no repetidos solo para la tabla que la contiene. Si una aplicación debe generar una columna de identificación que genere valores únicos para toda una base de datos o para todas las bases de datos en todo el mundo, se deberá utilizar la propiedad ROWGUIDCOL, el tipo de dato uniqueidentifier y la función NEWID.

Cuando se utiliza la propiedad ROWGUIDCOL para definir un identificador globalmente único se deberá tener en cuenta lo siguiente:

·        Una tabla puede tener una sola columna ROWGUIDCOL, y esta columna debe ser definida con el tipo de dato uniqueidentifier.

·        SQL Server no genera valores automáticamente para esta columna. Para insertar un valor globalmente único, se deberá crear una definición DEFAULT sobre la columna que utilice la función NEWID.

·        La columna puede ser referenciada en una lista de selección con la palabra clave ROWGUIDCOL después que la propiedad ROWGUIDCOL fue configurada. Este funcionamiento es similar al modo en que se puede referenciar una columna IDENTITY utilizando la palabra IDENTITYCOL.

·        Dado que la propiedad ROWGUIDCOL no asegura unicidad, el comando UNIQUE debería ser usado para determinar que serán insertados valores únicos en la columna con la propiedad ROWGUIDCOL.

El siguiente ejemplo utiliza el comando CREATE TABLE para crear la tabla Empleados. La columna Emp_ID automáticamente generará un valor globalmente único cuando se inserte una nueva fila.

CREATE TABLE
(
Emp_ID uniqueidentifier DEFAULT NEWID() NOT NULL,
EmpNombre varchar(609 NOT NULL
)

Métodos para crear tablas

SQL Server provee varios métodos para crear tablas: el comando Transact_SQL CREATE TABLE, el árbol de la consola del SQL Server Enterprise Manager, y el Database Designer (al cual se puede acceder a través del SQL Server Enterprise manager).

Comando CREATE TABLE

Se puede utilizar el comando CREATE TABLE para crear una tabla dentro de una base de datos SQL Server, Cuando se utiliza este comando, se debe definir, como mínimo, el nombre de la tabla, las columnas y el tipo de datos (y si corresponde sus valores).

El siguiente ejemplo muestra como crear una  tabla SQL:

CREATE TABLE Clientes
(
Cliente_ID char(4),
ClienteNombre varchar(40),
)

Además de los elementos básicos de la tabla (nombre, columnas y tipos de datos), el comando CREATE TABLE también permite definir otra propiedades. Por ejemplo, se puede especificar el grupo de archivos sobre el que se almacenará la tabla, o se pueden definir restricciones que se aplique a columnas individuales o a toda la tabla.

Enterprise Manager

Se pueden crear tablas directamente en el SQL Server Enterprise Manager. Para crear una tabla en una base de datos existente, expanda el árbol de la consola hasta que la base de datos aparezca, clic derecho sobre el nodo Tables, y clic sobre New Table (Nueva Tabla). Cuando la ventana New Table se abra, complete la información necesaria para definir la tabla.

Database Designer (Diseñador de base de datos)

Se puede utilizar el Database Designer en el SQL Server Enterprise Manager para agregar una tabla a un diagrama de base de datos, editar su estructura, o relacionarla a otras tablas en el diagrama. Se puede tanto insertar tablas existentes de la base de datos al diagrama como insertar una nueva tabla a la base de datos y al diagrama.  Además, desde el Table Designer se pueden modificar tablas existentes. Table Designer es una herramienta visual que permite diseñar y ver una tabla en una base de datos a la que uno debe estar conectado.

Administrar tablas de una base de datos SQL Server

Una vez que se ha creado una tabla en una base de datos SQL Server, se puede consultar información sobre la tabla, modificar sus características, o eliminar la tabla de la dbase de datos.

Consultar información sobre tablas

Después que ha creado las tablas de una base de datos, Ud. podría necesitar encontrar información acerca de las propiedades de una tabla (por ejemplo, el nombre o el tipo de dato de una columna, la naturaleza de sus índices, etc.) Se puede también mostrar las dependencias de la tabla, determinando que objetos (tales como vistas, procedimientos almacenados, y desencadenadores) tiene dependencia con la tabla. Advierta que si se realizan modificaciones sobre la tabla, los objetos dependientes pueden ser afectados.

SQL Server incluye varios métodos para ver las características de una tabla y de sus dependencias.

·        Para ver la definición de un tabla, utilice el procedimiento almacenado provisto por el sistema sp_help o el SQL Server Enterprise Manager para ver las propiedades de una tabla.

·        Para ver las dependencias de una tabla, utilice el procedimiento almacenado sp_depends o le opción Display Dependencies en el SQL Server enterprise Manager.

·        Para ver propiedades de las columnas, utilice el comando COLUMNPROPERTY para retornar información acerca de una columna o un parámetro de procedimiento.

Modificar tablas de una base de datos SQL Server

Después que se crea una tabla se pueden cambiar muchas opciones que fueron definidas para la tabla cuando esta fue originalmente creada, incluyendo las siguientes:

Pueden ser agregadas, modificadas o eliminadas columnas. Por ejemplo, el nombre de la columna, longitud, tipo de dato, precisión, escala, y anulabilidad pueden ser modificadas, aunque con algunas restricciones. 

·        Pueden ser agregadas o modificadas las restricciones PRIMARY KEY y FOREIGN KEY

·        Pueden ser agregadas o modificadas las restricciones UNIQUE y CHECK y la definición DEFAULT (y objetos).

·        Se puede agregar o borrar una columna de identificación usando las propiedades IDENTITY o ROWGUIDCOL. La propiedad ROWGUIDCOL puede ser agregada o removida de una columna existente, aunque sólo una columna en la tabla puede tener la propiedad ROWGUIDCOL al mismo tiempo.

·        Una tabla y las columnas seleccionadas dentro de la tabla pueden ser registradas para una indexación a texto completo(full-text indexing)

Se puede, también, cambiar el nombre y el dueño (owner) de una tabla. Cuando se ejecuta esta operación, se debe cambiar el nombre de tabla en todos los desencadenadores, procedimientos almacenados, Transact-SQL scripts, o cualquier otro código de programación que use el nombre o el dueño anterior de la tabla.

El siguiente cuadro provee una lista de varios tipos de modifcaciones que se pueden hacer sobre las propiedades de las tablas. El cuadro muestra, además, la lista de los métodos a utilizar a tal efecto. 

Tipo de Modificación	Método de Modificación
Renombrar una tabla	El procedimiento almacenado de sistema sp_rename. La opción Rename en el SQL Server Enterprise Manager
Cambiar el dueño de una tabla	El procedimiento almacenado de sistema sp_changeobjectowner
Modificar las propiedades de las columnas	El comando ALTER DATABASE  La opción Design Table en el SQL Server Enterprise Manager
Renombrar una columna	El procedimiento almacenado de sistema sp_rename La opción Design Table en el SQL Server Enterprise Manager

Borrar tablas de una base de datos SQL Server

A veces, se necesita borrar una tabla (por ejemplo, cuando se quiere implementar un nuevo diseño o liberar espacio en una base de datos). Cuando se elimina una tabla, la definición de su estructura, los índices a texto completo, las restricciones y los índices son borrados de manera permanente, y el espacio que ocupaban es liberado para otros objetos. Se puede explícitamente borrar una tabla temporaria si no se quiere esperar a que sea eliminada en forma automática.

Si se necesita eliminar tablas que se encuentran relacionadas a través de FOREIGN KEY y de las restricciones UNIQUE o PRIMARY KEY, se deben eliminar primero las tablas con la restricción FOREIGN KEY primero. Si se necesita eliminar una tabla que tiene la restricción FOREIGN KEY pero no se quiere eliminar la tabla con la clave ajena, primero se deberá eliminar la restricción FOREIGN KEY.

Para eliminar una tabla de una base de datos SQL Server se utiliza el comando DROP TABLE o el Enterprise Manager removiendo la tabla del nodo Tables.