Introducci%80%a0%a6%f3n A Tsql - Unidad 8

Introducción a TSQL Unidad 8

Autor Isabel Ponce

Unidad 8 Objetivos • Integridad • Integridad Declarativa • Default • Restricciones a nivel Columna • Restricciones a nivel Tabla • Restricción Not Null • Restricción Check • Restricción Unique • Restricción de Llave Primaria • Restricción de Llave Foránea • Integridad Referencial • Índices Agrupados • Índices No Agrupados

Integridad • Integridad es el proceso por el cual se validan los datos y se obliga su consistencia. • Las bases de datos fueron diseñadas con la integridad como un factor primario. • La integridad puede ser obligada mediante una variedad de objetos: – – – – – – –

Prohibir nulos (Not Null) Valores por omisión (Defaults) Reglas (Rules o Checks) Llaves Primarias (Primary keys) Llaves Foráneas (Foreign keys) Índices Únicos (Unique indexes) Disparadores (Triggers)

• La integridad puede ser también programática o declarativa.

Integridad Declarativa • Los defaults y restricciones pueden ser usados directamente en una declaración create table, definiendo una integridad declarativa. • Las restricciones incluyen: – – – – –

Not Null Check Unique Primary Key Reference

• Las restricciones pueden ser a nivel columna o tabla. • Los defaults son sólo a nivel columna.

Defaults

• Una cláusula DEFAULT es usada para proporcionar un valor por omisión a una columna cuando no se especifica explícitamente en una instrucción insert. • Para crear un DEFAULT: DEFAULT {expresiónConstante | función| NULL} create table Direccion (idDireccion int not null, direccion varchar(50) not null, ciudad varchar(30) default ‘Mexico', estado char(2) default ‘DF');

Defaults

• También se pueden usar algunas funciones en lugar de constantes siempre que retornen un sólo valor. • El valor por omisión debe coincidir con el tipo de dato de la columna. • Los valores caracter y fecha deben ir entre comillas. • Una columna sólo puede tener un valor por omisión.

Restricciones • En los casos de Check, Unique, Primary Key y Foreign Key, las restricciones serán de columna si sólo afectan a una columna, cuando afectan a más de una columna se convierten en restricciones de tabla. • En general, las restricciones de columna se pueden considerar como un caso especial de restricciones de tabla, cuando están compuestas por un solo atributo se pueden especificar tanto al lado de la columna como parte de su definición o al finalizar la definición de atributos.

Restricción de nulos

• La restricción NOT NULL evita que se introduzcan valores nulos en las columnas que definan esta restricción; hay que recordar que los valores nulos pueden provocar resultados inconsistentes, por lo que en ocasiones no se puede permitir que las columnas no tengan algún valor

• Para prohibir valores NULOS: <nombreColumna> NOT NULL CREATE TABLE Cliente ( idCliente VARCHAR(5) NOT NULL, ciudad CHAR(2), repVentas VARCHAR(4), telefono VARCHAR(12) );

Restricciones Check • Las restricciones check se usan para obligar la integridad de dominio. • Pueden ser aplicadas a nivel tabla y columna • Los ckeck son usados para especificar: – – – –

Listas o conjuntos de valores (IN) Rangos de valores (BETWEEN) Formatos para los datos (LIKE) Condiciones sobre un valor (=,<,>)

• Restringen los valores durante las inserciones y modificaciones. • Deben poderse evaluar como falso o verdadero.

Restricciones de Columna • Se puede usar sobre columna, creando restricción de columna.

una una

create table persona (rfc char(11) not null check (rfc like '[A-Z][A-Z][A-Z] [A-Z]-[0-9][0-9]-[0-9][0-9]-[0-9] [0-9]‘), nombre varchar(30) not null, apellido varchar(50) not null);

Restricciones de Tabla • Se puede usar para más de una columna, creando una restricción de tabla y no una restricción de columna. create table descuentos (tipo varchar(40) not null, idTienda char(4) not null, cantMenor int not null, cantMayor int not null, descuento float null, check (cantMenor <= cantMayor));

Precedencia • Durante una inserción, se verifica primero default y después check. • Durante una modificación, se verifica primero el check. • Si se define un default o chek sobre una columna, éste sobrescribe la definición del tipo de dato. • El último check o default definidos son los que son los efectivos.

Restricción Unique • Evita que dos renglones tengan el mismo valor. • Permite un solo valor nulo en la columna. • Crea un índice único. create table editoriales (idEditorial char(4) null, constraint u_id unique (idEditorial), editorial varchar(30) not null); ó create table editoriales (idEditorial char(4) null unique , editorial varchar(30) not null)

Restricción Unique CREATE TABLE TarjetaDeCredito (banco varchar(25) not null, numeroTarjeta varchar(15) not null, limiteCredito numeric(4,2), numeroPin char(12) not null, PRIMARY KEY (numeroTarjeta), UNIQUE (numeroPin) );

Llaves Primarias (PK) • Evita que dos renglones tengan el mismo valor. • No permite valores nulos. • Crea un índice único. create table Editoriales( idEditorial char(4) primary key, editorial varchar(30)); create table Ventas (idTienda char(4) not null, numOrden varchar(20) not null, fecha datetime not null, constraint pkVentas primary key (idTienda, numOrden));

Llaves Foráneas (FK)

• De acuerdo a la regla de integridad referencial, una columna no nula de una clave foránea debe ser un valor real de la clave en otra relación. • SQL permite definir llaves foráneas o externas y obliga que los valores introducidos en las columnas marcadas con esta restricción correspondan a valores en la tabla referenciada, además permite definir la acción a realizar en caso de actualizaciones o eliminaciones de los valores a los que hacen referencia. • Las llaves foráneas se usan para mantener la consistencia entre renglones de dos relaciones o entre renglones de la misma relación. • Las llaves foráneas pueden definirse al momento de crear la tabla o después de que ha sido creada

Llaves Foráneas (FK)

• La integridad referencial se usa para mantener llaves foráneas cuando se insertan o modifican los datos. • Columna create table <nombre_tabla> ( [constraint <nombre_restricción>] references (columna_ref) • Tabla create table <nombre_tabla> (*) [constraint <nombre_restricción>] foreign key (*) references (*)

Llaves Foráneas (FK)

• Usar la restricción a nivel columna cuando sólo una columna necesita ser comparada. • Usar la restricción a nivel tabla cuando más de una columna necesita ser comparada. • La tabla en la cláusula references debe tener una restricción de llave primaria/unique o un índice único definido en las columnas. • Se realiza un roll back si se viola la integridad referencial y se envía un mensaje.

LLaves Foráneas (FK) • A nivel columna create table Titulos (idTitulo int not null primary key, titulo varchar(80) null, idEditorial char(4) null references Editoriales(idEditorial), notas varchar(200) null);

• Restricciones create table DetalleVentas (idTienda char(4) not null, numOrden varchar(20) not null, idTitulo int not null, cantidad int not null, descuento float not null, constraint fkVentas foreign key (idTienda, numOrden) references Ventas(idTienda, numOrden), constraint fkTitulos foreign key (idTitulo) references Titulos(idTitulo));

Integridad Referencial • En lo que respecta a las acciones que se deben tomar en caso de eliminación o modificación de los valores de las columnas referenciadas, SQL admite cuatro posibilidades: • RESTRICT o NO ACTION – indica operación no válida, si no se especifica, este es el valor por omisión. • CASCADE – indica operación con transmisión en cascada. • SET NULL – indica operación con inserción de valores nulos. • SET DEFAULT – operación con inserción del valor por omisión. • En un mundo perfecto, las modificaciones a una llave primaria no están permitidas. • En un mundo imperfecto, esto debe evitarse al máximo.

Integridad Referencial • Para especificar la acción a tomar en caso de eliminación o modificación se indicará: references (columna_ref) ON UPDATE ON DELETE donde ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION | RESTRICT

Integridad Referencial CREATE TABLE Documento (tipo CHAR(1) CHECK (tipo IN (‘A’, ‘L’)), codDoc CHAR(4), titulo CHAR(25) NOT NULL, editorial CHAR(4), anio INTEGER(4) CHECK (anio > 1950), isbn INTEGER(10), PRIMARY KEY(tipo, codDoc), UNIQUE (isbn), CHECK ((tipo = ‘A’ AND isbn IS NULL AND editorial IS NULL) OR (tipo = ‘L’ AND isbn IS NOT NULL AND editorial IS NOT NULL)), FOREIGN KEY(editorial) REFERENCES Editorial (idEditorial) ON UPDATE CASCADE ON DELETE NO ACTION );

Índices • Un índice es una estructura interna que el DBMS usa para encontrar uno o más tuplas rápidamente. Se crea para una o más columnas y es similar al índice de un libro. El DBMS lee el índice para determinar las posiciones de las tuplas de una consulta. • Los nombres de índices siguen las mismas reglas que los nombres de tablas. Para crear un índice se puede usar la instrucción CREATE INDEX. En general, es mejor crear los índices de una tabla antes de insertar datos, sin embargo, se puede crear en cualquier momento. Una vez creado el índice, el sistema lo mantiene automáticamente.

Índices • Un índice es una estructura separada que depende de una tabla. • Contiene apuntadores a los datos físicos. • Se usan para incrementar el desempeño cuando: – Se localizan renglones – Se correlacionan datos entre tablas (JOIN). – Se ordenan los resultados – Se insertan datos en algunos casos

• Pueden obligar a que existan valores únicos en una columna o tabla. CREATE [UNIQUE] INDEX <nombre_índice> ON (*);

Índices CREATE TABLE T1 ( col1 int, INDEX (c1)); CREATE UNIQUE INDEX IdxCliente ON Cliente (idCliente); ALTER TABLE Cliente ADD INDEX IdxCliRepVentas (repVentas); ALTER TABLE Cliente ADD INDEX (nombre(2)); #sobre los 2 primeros caracteres DROP INDEX IdCliRepVentas ON Cliente; También se puede eliminar con: ALTER TABLE Cliente DROP INDEX IdCliRepVentas;

Costo de los Índices

• Aunque se pueden crear cualquier cantidad de índices sobre una tabla, el crear un índice implica un costo adicional: – Espacio. Aunque más pequeño que las tablas, los índices requieren espacio de almacenamiento en disco. Si una tabla tiene muchos índices, estos pueden exceder el tamaño de la tabla. – Costo de actualización. Aunque facilitan las consultas, dificultan el proceso de actualización, ya que cada operación de actualización requiere modificar los índices.

Dónde usar los Índices

• En la cláusula WHERE. • Al usar MIN y MAX. • Cuando todas las columnas están en el índice. • Al usar ORDER BY. • Cuando se usa JOIN. • Al usar comodines.

Seleccionar Índices • Algunos consejos para seleccionar los índices son: – Sólo deberían crearse en consultas que los utilicen (columnas de la condición WHERE) y no sobre columnas que no vayan a usarse. – Generarlos cuando devuelvan el menor número de tuplas posibles. Es mejor sobre PKs ya que se asocian exclusivamente a una tupla; no conviene hacerlo sobre enumerados que tienen múltiples valores repetidos. – Es mejor un índice sobre los 10 primeros caracteres que sobre la columna completa. – Mejor no muchos índices ya que incrementan en tiempo de actualización o inserción. – Si se crea sobre varias columnas, serán mejor si generalmente se consultan en el orden especificado en el que se especificaron en el índice.

Tipos de Índices • Existen 3 tipos excluyentes: – Índice compuesto. El que se crea sobre varias columnas. En realidad es un índice simple en el que la clave e compone de datos que se encuentran en varias columnas. En este caso, el orden de las columnas es importante, y generalmente se tiene un límite de columnas a usar. – Índice único. El que no puede tener valores duplicados. Si se intenta ingresar valores duplicados, la instrucción fallará. Se pueden crear varios índices únicos por tabla que pueden corresponder con las claves secundarias o alternas. – Índice agrupado. Cuando se ejecuta la carga o reorganización, el sistema almacenará las tuplas de la tabla en el orden físico indicado con la clave del índice agrupado. En ciertas consultas, el optimizador puede usar el conocimiento de los registros almacenados en determinada secuencia.

Índice Único • Los índices únicos son usados para obligar la unicidad de una columna o grupo de columnas. • Un índice único se crea en tres situaciones: – Cuando se define una restricción de llave primaria. – Cuando se define una restricción unique. – Cuando se crea explícitamente un índice único.

• Cuando se crea un índice único, se verifica que los valores sean únicos. Si existen valores duplicados, la creación del índice falla. • Una vez creado, se verifica que en las subsecuentes inserciones y modificaciones no se dupliquen los valores.

Resumen Unidad 8 • Las restricciones se usan para obligar la integridad. • Si una restricción se indica al declarar una tabla se llama declarativa. • Un default asigna un valor durante un insert y puede ser creado como un objeto separado. • Las restricciones pueden definirse sobre una columna o sobre toda la tabla. • La restricción not null evita que se puedan almacenar registros sin valor en esa columna. • La restricción check permite validar datos durante insert y update.

Resumen Unidad 8 • La restricción Unique crea un índice único y sólo permite un valor nulo. • La restricción de Llave Primaria crea un índice único no permite valores nulos. • Las Llaves Foráneas son definidas mediante una restricción de referencia (references). • La(s) columna(s) referenciada(s) debe(n) tener una restricción de llave primaria/unique o definido un índice único. • Los índices mejoran el desempeño de las operaciones de consulta. • Los índices puedens ser compuestos, únicos o agrupados.