Unidad 8. El DDL, Lenguaje de Definición de Datos (VIII)

Si no se especifica CLUSTERED o NONCLUSTERED, de forma predeterminada se utiliza NONCLUSTERED.
Como cada restricción UNIQUE genera un índice. El número de restricciones UNIQUE no puede hacer que el número de índices de la tabla exceda de 249 índices no agrupados y 1 índice agrupado.
Si se define una restricción UNIQUE en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario o basado en el operador.
Las demás opciones de índices:

    [ WITH FILLFACTOR = factorRelleno  

       | WITH ( < opcion_indice > [ , ...n ] ) ]

    < opcion_indice > ::=

      { 

          PAD_INDEX = { ON | OFF } 

        | FILLFACTOR = factorRelleno  

        | IGNORE_DUP_KEY = { ON | OFF } 

        | STATISTICS_NORECOMPUTE = { ON | OFF } 

        | ALLOW_ROW_LOCKS = { ON | OFF} 

        | ALLOW_PAGE_LOCKS ={ ON | OFF} 

      }  

Son cláusulas que nos permiten definir con más detalle el índice pero que no veremos aquí por entrar demasiado en cuestiones internas.

La cláusula CHECK.

    CHECK [ NOT FOR REPLICATION ] ( expresion_validacion ) 

Con CHECK indicamos una regla de validación que deberán cumplir todas las filas de la tabla, es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en la columna.
expression es una expresión lógica que devuelve TRUE o FALSE.
Si queremos definir una restricción CHECK sobre una columna calculada, esta se deberá definir como PERSISTED.
Una columna puede tener cualquier número de restricciones CHECK y la condición puede incluir varias expresiones lógicas combinadas con AND y OR. Varias restricciones CHECK para una columna se validan en el orden en que se crean.
La condición de búsqueda debe evaluarse como una expresión booleana y no puede hacer referencia a otra tabla.
Una restricción CHECK en el nivel de columna sólo puede hacer referencia a la columna restringida y una restricción CHECK en el nivel de tabla sólo puede hacer referencia a columnas de la misma tabla.
Las restricciones CHECK no se pueden definir en las columnas text, ntext o image.

Por ejemplo queremos que la columna Precio de la tabla que estamos definiendo no pueda contener valores negativos:

    …

      Precio CURRENCY CONSTRAINT precio_pos CHECK  (Precio > = 0)

      …  

Por último a nivel de columna podemos definir una restricción de clave ajena:

    [ FOREIGN KEY ] 

              REFERENCES [ nbEsquema.] nbTablaPadre [ ( col_padre ) ] 

              [ ON DELETE { NO  ACTION | CASCADE | SET NULL | SET DEFAULT } ] 

              [ ON UPDATE { NO  ACTION | CASCADE | SET NULL | SET DEFAULT } ] 

              [ NOT FOR  REPLICATION ]   

Con esta cláusula definimos una regla de integridad referencial, los valores contenidos en la columna deberán apuntar a un registro en la tabla de referencia (la tabla padre).
La palabra FOREIGN KEY no es obligatoria cuando estamos a nivel de columna, el utilizarla o no tiene el mismo efecto.
Nbesquema es el nombre del esquema al que pertenece la tabla padre y nbTablaPadre es el nombre de la tabla padre.

Col_padre es la columna de la tabla padre que se relaciona con la columna que estamos definiendo. Esta columna debe tener el mismo tipo de datos que la columna en la que se define la restricción. 
La col_padre debe tener en la tabla padre una restricción de PRIMARY KEY o UNIQUE, si no se indica una columna padre (columna de referencia) se utiliza la clave primaria de la tabla padre.
Las reglas de integridad referencial obligan a que si se especifica un valor distinto de NULL en una columna con una restricción FOREIGN KEY, ese valor debe existir en la columna referenciada de la tabla padre; de lo contrario, se devolverá un error de infracción de clave externa.
Las restricciones FOREIGN KEY sólo pueden hacer referencia a las tablas de la misma base de datos en el mismo servidor. La integridad referencial entre bases de datos debe implementarse a través de desencadenadores.
Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla. Esto se denomina autorreferencia.       

    create table personas (codigo integer primary Key,

      dni integer UNIQUE,

      madre integer references personas,

      padre integer references personas (col1),

      dniTutor integer references personas  (col4));   

En esta tabla de personas, identificamos a cada persona por un código, también tenemos el dni de la persona y el código de su padre y de su madre, además nos guardamos el dni del tutor de la persona. Las madres, padres y tutores son personas que deben de existir en la tabla Personas y en este caso utilizamos el código para referenciar padres y madres y el dni para referencial el tutor, esto último lo podemos hacer porque el campo dni tiene una restricción UNIQUE.
Por último nos queda completar las reglas de integridad referencial en cuanto a qué hacer cuando se eliminan o modifican valores que intervienen en una relación referencial.

    ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }   

Indica qué ocurre cuando se intenta eliminar un registro padre en la relación que estamos definiendo. El valor predeterminado es NO ACTION.
NO ACTION El Motor de base de datos genera un error y  no es posible eliminar la fila de la tabla primaria (el padre).
CASCADE  Si se borra una fila de la tabla primaria, se eliminan automáticamente todas las filas correspondientes de la tabla que estamos definiendo, en otras palabras, si se elimina un padre, se eliminan todos sus hijos.
 SET NULL Si se borra una fila de la tabla primaria, todas las filas correspondientes de la tabla que estamos definiendo tomarán el valor NULL en el campo clave ajena. En otras palabras, si se elimina un padre, sus hijos se quedan sin padre.
Para ejecutar esta restricción, la columna clave ajena debe admitir valores NULL.
SET DEFAULT Es como la anterior pero en vez del valor NULL toman el valor que tienen predeterminado. Si no hay ningún valor predeterminado establecido de forma explícita, tomarán el valor NULL. Hay que tener en cuenta que el valor predeterminado debe de existir en la tabla primaria.

 Cursos Informática Gratuitos