- Microsoft Windows Forms y Windows Presentation aplicaciones Foundation (WPF)
- Las páginas ASP.NET y Silverlight
- JavaScript embebido en Hypertext Markup Language (HTML)
- Los componentes de negocio (por ejemplo, los ensamblados. NET o componentes COM)
- Bases de datos
Validar datos
Hay dos formas de validar la integridad de los datos en SQL Server, ya sea usando la integridad de datos declarados o comprobando la misma mediante procedimientos. También puede implementar integridad de datos declarados con reglas (con la instrucción CREATE RULE), sin embargo, las reglas no deben utilizarse porque se eliminarán de SQL Server en una versión futura.
La Integridad de los datos por procedimiento se lleva a cabo ya sea al permitir que un procedimiento almacenado valide datos o mediante la creación de los triggers que comprobarán los datos en antes o después de que un lenguaje de manipulación de datos (DML) (tales como INSERT, UPDATE o DELETE) se emita.
En general, la integridad de los datos declarados es la simple comprobación de la integridad al insertar nuevos datos, ya que requiere un esfuerzo leve y muy poco desarrollo. Esto también hace que sea menos probable que produzcan errores, ya que contiene menos código que la integridad de los datos mediante procedimiento. Por otro lado, la integridad de los datos por procedimiento por lo general permite un mayor control de integridad avanzada. La aplicación de base de datos típica tiene que usar tanto la integridad de datos declarativo y como mediante procedimientos (Almacenados y triggers).
Implementando la integridad de los datos mediante declaraciones.
La integridad de los datos mediante declaraciones se implementa con las restricciones. Hay cinco tipos de restricciones:
PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, y DEFAULT.
PRIMARY KEY y UNIQUE:
Mediante las PRIMARY KEY y las restricciones UNIQUE se identifica una columna o combinación de columnas una fila de una tabla de manera unívocamente . Esto se aplica a través de la creación de un índice UNIQUE, es decir, un índice que no permite valores duplicados. Debido a esto, una restriccion de las PRIMARY KEY y UNIQUE es la limitacion de tamaño igual a la clave de un índice, es decir, que no puede contener más de 16 columnas y 900 bytes de datos.
Si no hay otra cosa especificada, el índice que se crea para una PRIMARY KEY es un índice agrupado y el índice de una restricción UNIQUE es un índice no agrupado. Sin embargo, puede cambiar este comportamiento especificando el tipo de índice para crear en el ALTER TABLE o CREATE TABLE, de la siguiente manera:
-- Primary key como un clustered index (indice agrupado). ALTER TABLE MyTable ADD PRIMARY KEY (MyTableID); -- Primary key como un nonclustered index(indice no agrupado). ALTER TABLE MyTable ADD PRIMARY KEY NONCLUSTERED (MyTableID);
Dado que las PRIMARY KEY y las restricciones UNIQUE son las restricciones y los índices, se puede encontrar información sobre ellos, tanto en el catálogo sys.key_constraints y en el catálogo de vista sys.indexes.
Restricciones PRIMARY KEY:
Las restricciones FOREIGN KEY identifican una columna o combinación de columnas cuyos valores deben existir en otra columna o combinación de columnas en la misma mesa o en otra tabla en la misma base de datos. Las restricciones FOREIGN KEY gestionan la integridad referencial entre las tablas o en una sola tabla. Para llevar a cabo una restricción de de clave foránea, debe seguir las siguientes reglas:
- Las columnas que se hace referencia debe tener exactamente el mismo tipo de datos (y la colación, para las columnas de cadenas de caracteres), como las columnas locales.
- Las columnas que se hace referencia debe tener un índice único creado en ellos. Esto es típicamente implementado utilizando una clave principal o una restricción única.
- Debido a que la de clave foránea debe hacer referencia a un índice único, las columnas de de clave foránea tienen las limitaciones de tamaño igual a la de las restricciones de clave principal y único.
Las claves foráneas son por lo general consultadas con frecuencia en las consultas del usuario y en los JOINS, así como cuando SQL Server tiene que verificar la integridad referencial al borrar o actualizar las filas claves primarias.
Esto significa que las claves foráneas por lo general se benefician enormemente de la indexación.
Cuando una restricción de de clave foránea observa una violación de la integridad referencial a causa de un DELETE o un UPDATE en una fila que hace referencia, la reacción por defecto es generar un mensaje de error y hacer retroceder la afirmación de que violó la restricción. Si este no es el resultado que usted desea, puede cambiar la acción predeterminada para la clave externa para eliminar la fila de referencia, actualizar la columna de referencia, o ambas cosas. Hay cuatro acciones para elegir:
- NO ACTION (Muestra un mensaje de error, opcion por defecto).
- SET NULL
- SET DEFAULT
- CASCADE
CREATE TABLE Test.Customers( CustomerID INT PRIMARY KEY ); CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NULL REFERENCES Test.Customers ON DELETE SET NULL ON UPDATE CASCADE );
El comportamiento predeterminado de la clave foránea es NO ACTION. Si la clave externa encuentra una violación y NO ACTION es especificado, SQL Server deshace la declaración de que violó la restricción y genera un mensaje de error.
SET NULL y SET DEFAULT causa para todos los valores de referencia los valores NULL (por SET NULL) o valores DEFAULT (para SET DEFAULT, es decir, el valor predeterminado definido en la columna) en lugar de generar un error y hacer retroceder la consulta. En la relación entre las tablas Customers y Orders se muestra en el ejemplo de código, si un cliente se elimina, la columna CustomerID se establece en NULL para todos los pedidos que pertenecen a ese cliente y ningun mensaje de error se envía a la aplicación que ocasiono la consulta.
La acción CASCADE hace que SQL Server elimine las filas de referencia de una sentencia DELETE (ON DELETE) y actualizar los valores de referencia (ON UPDATE) de una instrucción UPDATE.
Usando el ejemplo de código mismo, si la columna CustomerID se modifica para una fila de la tabla de clientes, todas las filas correspondientes de la tabla Orders se actualizan con el mismo CustomerID para reflejar el cambio. Si ON DELETE CASCADE se especifica para la restricción de clave externa y una fila de la tabla de clientes se elimina todas las filas de referencia en la tabla Pedidos se eliminan.
Esto puede sonar razonable, pero tal vez no sea posible llevar a cabo en cascada para todas las restricciones de de clave foránea porque las referencias cíclicas no son compatibles. Por ejemplo, en la siguiente secuencia de comandos, se produce un error cuando intenta agregar la clave foránea FKCustomersLastOrder , ya que introduce una referencia cíclica. Si un cliente se elimina, todos los pedidos de referencia debe ser eliminado, y todos los clientes de referencia a los pedidos a través de la columna LastOrderID también debe ser borrada:
CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY ,LastOrderID INT NULL ); CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NOT NULL REFERENCES Test.Customers ON DELETE CASCADE ON UPDATE NO ACTION ); ALTER TABLE Test.Customers ADD CONSTRAINT FKCustomersLastOrder FOREIGN KEY (LastOrderID) REFERENCES Test.Orders (OrderID) ON DELETE CASCADE ON UPDATE NO ACTION;
En el ejemplo anterior, considere lo que sucede si un cliente se elimina, todos los pedidos del cliente también se eliminan. Esto podría estar bien, pero tenga en cuenta el siguiente código:
CREATE TABLE Test.Countries ( CountryID INT PRIMARY KEY);
CREATE TABLE Test.Cities ( CityID INT PRIMARY KEY ,CountryID INT NOT NULL REFERENCES Test.Countries ON DELETE CASCADE);
CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY ,CityID INT NOT NULL REFERENCES Test.Cities ON DELETE CASCADE); CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NOT NULL REFERENCES Test.Customers ON DELETE CASCADE);
En este ejemplo, si elimina un país, todas las ciudades de ese país, todos los clientes en esas ciudades, y todos los pedidos que pertenecen a los clientes también se eliminan. Tenga cuidado con que podría estar eliminando más de lo que piensas. Considere la posibilidad de que alguien que ejecute el un comando DELETE CountryId = 1 desde SSMS. La persona podría pensar que está eliminando una sola fila en la tabla de los países, cuando él o ella podría ser en realidad la eliminación de millones de las filas. El tiempo que tarda en ejecutarse esta instrucción DELETE depende del número de filas que se eliminan. Cuando termina, SSMS devuelve el siguiente mensaje:
(1 row(s) affected)
Este mensaje se devuelve, aunque millones de filas se han eliminado porque el mensaje sólo nos dice cuántas filas se han eliminado directamente por la sentencia ejecutada. No hay nada malo con este comportamiento, pero es definitivamente algo que usted debe considerar.Triggers: Si ha definido claves foráneas con las acciones en cascada, los triggers AFTER en las tablas afectadas siguen siendo ejecutado, pero se ejecutan después de que toda la cadena de acciones en cascada se han completado. Si ocurre un error durante la cadena de acción en cascada se está ejecutando, toda la cadena se deshace y ningún triggers AFTER se ejecuta de esa cadena.Chequeando limitaciones:
Las restricciones de comprobación son un conjunto de reglas que deben ser validados antes los datos que se les permita acceder a una tabla. Las ventajas de usar las restricciones de comprobación son las siguientes:
- Son fáciles de implementar. (Son muy similares a una cláusula WHERE.)
- Se activa automáticamente.
- Pueden mejorar el rendimiento.
ALTER TABLE Products ADD CHECK(Price >= 0.0);
La simplicidad de las restricciones de comprobación es una gran ventaja sobre el uso de los Triggers. Sin embargo, hay algunas desventajas, tales como las siguientes:
- Mensajes de error de las restricciones CHECK son generados por el sistema y no puede ser sustituido por un mensaje de error más amigable.
- Una restricción de comprobación no puede "ver" el valor anterior de la columna. Esto significa que no puede ser utilizado para algunos tipos de reglas de integridad de datos, tales como "Las actualizaciones de la columna de precios no puede aumentar o disminuir el precio en más del 10 por ciento".
Por ejemplo, si usted tiene una restricción de comprobación que indica que Price>10.0, puede insertar un valor NULL en la columna de Precio. Este valor está permitido porque cualquier comparación que se hace con NULL devuelve NULL, no es ni verdadero ni falso. Si usted no desea que la restricción de comprobación permita valores NULL, puede o bien deshabilitar NULL en la columna Precio, especificando la restricción NOT NULL para la columna o al cambiar la restricción de comprobación para leer Price>10.0 y el precio no NULL.
Extendiendo restricciones CHECK Con funciones definidas por el usuario
Las funciones definidas por el usuario (UDF), creado tanto en T-SQL o por código administrado (también conocido como. NET o UDF CLR) puede ser una parte integral de las restricciones CHECK. La expresión de una restricción CHECK puede contener la mayor parte de la lógica que se puede utilizar en una cláusula WHERE (incluyendo NOT, AND y OR). Se pueden llamar UDF escalares y hacer referencia a otras columnas en la misma tabla, sin embargo, no pueden contener subconsultas directamente. Debido a que usted puede escribir sus propias funciones escalares, ya sea en T-SQL o mediante código administrado, puede aplicar la lógica avanzada dentro de las restricciones de su CHECK y, a través de ellos, incluso el uso de subconsultas.
El siguiente ejemplo se crea un UDF llamado fnIsPhoneNumber en código administrado (como se muestra en Microsoft Visual Basic y C #) para verificar que una cadena contiene un número válido de teléfono de EE.UU. mediante la aplicación de una expresión regular:
‘VB <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, _ DataAccess:=DataAccessKind.None)> _ Public Shared Function fnIsPhoneNumber(ByVal phoneNumber As SqlString) _ As SqlBoolean If (phoneNumber.IsNull) Then Return SqlBoolean.Null End If Return System.Text.RegularExpressions.Regex.IsMatch(phoneNumber.Value, _ "^\([1-9]\d{2}\)\s?\d{3}\-\d{4}$") End Function
// C# [SqlFunction(IsDeterministic = true, DataAccess=DataAccessKind.None)] static public SqlBoolean fnIsPhoneNumber(SqlString phoneNumber) { if (phoneNumber.IsNull){ return SqlBoolean.Null; } return System.Text.RegularExpressions.Regex .IsMatch(phoneNumber.Value, @"^\([1-9]\d{2}\)\s?\d{3}\-\d{4}$"); }
El siguiente código crea una tabla y la restricción CHECK que haga referencia a la UDF:
CREATE TABLE Test.Contacts ( ContactID INT IDENTITY PRIMARY KEY ,Name NVARCHAR(50) NOT NULL ,PhoneNumber VARCHAR(20) NULL ,CONSTRAINT CKContactsPhoneNumber CHECK(dbo.fnIsPhoneNumber(PhoneNumber) = CAST(1 AS BIT)));
-- Allowed: INSERT Test.Contacts (Name, PhoneNumber) VALUES ('Tobias', '(425)555-1111'); INSERT Test.Contacts (Name, PhoneNumber) VALUES ('Chris', NULL); -- Disallowed, will raise an error: INSERT Test.Contacts (Name, PhoneNumber) VALUES ('Ann', '(42)555-2222');
Cuando se ejecuta esta restricción de comprobación? Sólo cuando es necesario. El optimizador ejecuta la restricción de verificación sólo si las columnas se hace referencia en las restricciones de comprobación se hace referencia a la instrucción DML ejecutadas. Para inserciones, esto es siempre así, porque un INSERT siempre afecta a todas las columnas (incluso si se inserta un valor NULL). Para los UPDATES, la restricción CHECK sólo se ejecuta si una columna contenida en la restricción CHECK se hace referencia a la actualización.
Añadir mucha lógica a las limitaciones de su CHECK puede perjudicar el rendimiento. Un buen método consiste en añadir las restricciones necesarias y luego ejecuta una prueba de rendimiento para comprobar que el rendimiento es suficiente.Usando funciones definidas por el usuario con una SubConsulta
Es posible incluir subconsultas en las restricciones CHECK, colocándolos dentro de una UDF. Esta práctica puede resultar en un rendimiento pobre, porque la subconsulta se ejecuta una vez para cada fila afectada por una instrucción UPDATE o INSERT en la tabla. Imagina que quieres ampliar el ejemplo anterior para validar también los códigos de área del teléfono utilizando una subconsulta.
Los códigos de área de apoyo se almacenan en una tabla independiente denominada Test.AreaCodes. Esta es la versión extendida de la UDF:
‘ VB <Microsoft.SqlServer.Server.SqlFunction(IsDeterministic:=True, _ DataAccess:=DataAccessKind.Read)> _ Public Shared Function fnIsPhoneNumber2(ByVal phoneNumber As SqlString) _ As SqlBoolean If (phoneNumber.IsNull) Then Return SqlBoolean.Null End If If Not System.Text.RegularExpressions.Regex.IsMatch(phoneNumber.Value, _ "^\([1-9]\d{2}\)\s?\d{3}\-\d{4}$") Then Return False Else Dim areaCode As String = phoneNumber.Value.Substring(1, 3) Using conn As SqlConnection = New SqlConnection("context connection=true;") Using cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = _ "IF EXISTS(SELECT * FROM Test.AreaCodes " & _ " WHERE AreaCode = @AreaCode) " & _ " SELECT CAST(1 AS BIT) AS Found " & _ "ELSE " & _ " SELECT CAST(0 AS BIT) AS Found" cmd.Parameters.Add("@AreaCode", SqlDbType.Char, 3).Value = areaCode conn.Open() Return CType(cmd.ExecuteScalar(), Boolean) End Using End Using End If End Function
// C# [SqlFunction(IsDeterministic = true, DataAccess=DataAccessKind.Read)] static public SqlBoolean fnIsPhoneNumber2(SqlString phoneNumber) { if(phoneNumber.IsNull) return SqlBoolean.Null; if(!System.Text.RegularExpressions.Regex .IsMatch(phoneNumber.Value, @"^\([1-9]\d{2}\)\s?\d{3}\-\d{4}$")){ return false; }else{ string areaCode = phoneNumber.Value.Substring(1,3); using(SqlConnection conn = new SqlConnection( @"context connection=true;")) { using(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @"IF EXISTS(SELECT * FROM Test.AreaCodes WHERE AreaCode = @AreaCode) SELECT CAST(1 AS BIT) AS Found ELSE SELECT CAST(0 AS BIT) AS Found"; cmd.Parameters.Add("@AreaCode", SqlDbType.Char, 3) .Value = areaCode; conn.Open(); return (bool)cmd.ExecuteScalar(); } } } }
El siguiente código crea una tabla y la restricción CHECK haciendo referencia a la UDF:
CREATE TABLE Test.AreaCodes ( AreaCode CHAR(3) NOT NULL PRIMARY KEY ); -- Solo esta permitido con codigo de area. INSERT Test.AreaCodes (AreaCode) VALUES ('425'); CREATE TABLE Test.Contacts ( ContactID INT IDENTITY PRIMARY KEY ,Name NVARCHAR(50) NOT NULL ,PhoneNumber VARCHAR(20) NULL ,CONSTRAINT CKContactsPhoneNumber CHECK(dbo.fnIsPhoneNumber2(PhoneNumber) = CAST(1 AS BIT)) ); -- Permitido: INSERT Test.Contacts (Name, PhoneNumber) VALUES ('Ann', '(425)555-1111'); -- No permitido porque tiene un codigo invalido: INSERT Test.Contacts (Name, PhoneNumber) VALUES ('Chris', NULL);
Una consideración muy imporante al usar subconsultas en las restricciones CHECK es que, mientras que la restricción CHECK se verifica para las UPDATE e INSERT en la tabla, no se verifica cuando se eliminan las filas de la tabla que hace referencia la subconsulta. Los datos que la restricción CHECK valida con el INSERT o UPDATE se pueden eliminar sin que se produzca un error. Por ejemplo, la siguiente instrucción DELETE no da lugar a un error:
DELETE Test.AreaCodes WHERE AreaCode = '425';
Sin embargo, después de ejecutar la sentencia DELETE, la instrucción UPDATE siguiente genera un error:
UPDATE Test.Contacts SET PhoneNumber = PhoneNumber;
Este comportamiento es altamente indeseable, ya que crea que puede tener la misma protección que tiene con claves foráneas, que le protege contra la sentencia DELETE así.
En SQL Server 2008 (y 2005), a menudo se pueden sustituir esta lógica mediante el uso de una clave externa, como se describe en la siguiente sección.
Usando FOREIGN KEY con una SubConsulta
Vamos a implementar la validación del número de teléfono como una combinación de una restricción CHECK y una restricción de clave foránea. Se utiliza la primera versión de la UDF (el que no tiene subconsulta) con una clave externa. ¿Cómo se puede poner en práctica la clave foránea? Usted quiere que revisar el código de área en la tabla Test.AreaCodes, no el número de teléfono completo. Esto se hace mediante la aplicación de una columna calculada que devuelve sólo la parte de código de área del número de teléfono. Que tiene que hacer un par de cosas para hacer posible la creación de la clave foránea se muestra en el ejemplo.
El resultado de la expresión en la columna AreaCode deben ser del mismo tipo que la columna que hace referencia la de clave foránea, CHAR (3). Usted debe garantizar que este invocando la función CAST en la expresión AreaCode. La columna también debe estar marcado como PERSISTED, lo que significa que SQL Server almacenara físicamente el resultado de la expresión de la columna calculada en la fila de datos en lugar de calcular que cada vez que se le hace referencia en una consulta. Se vuelve a calcular cada vez que se actualiza la columna. Una de las razones de este requisito es que afecta el desempeño, usted no desea que SQL Server ejecute la función SUBSTRING cada vez que la clave foránea tenga que ser validada.
El siguiente script crea la nueva versión de la tabla Test.Contacts, incluyendo el agregado clave foránea:
CREATE TABLE Test.Contacts ( ContactID INT IDENTITY PRIMARY KEY ,Name NVARCHAR(50) NOT NULL ,PhoneNumber VARCHAR(20) NULL ,CONSTRAINT CKContactsPhoneNumber CHECK(dbo.fnIsPhoneNumber(PhoneNumber) = 1) ,AreaCode AS CAST(SUBSTRING(PhoneNumber, 2, 3) AS CHAR(3)) PERSISTED ,CONSTRAINT FKContactsAreaCodes FOREIGN KEY (AreaCode) REFERENCES Test.AreaCodes);
Como puede ver, la columna AreaCode en la tabla de contactos es sólo un subconjunto de la columna PhoneNumber.
¿Qué ocurre si se inserta un valor NULL en la columna PhoneNumber? La función SUBSTRING devuelve NULL, y NULL es aceptado por la clave foránea y se interpreta como un valor que no hace referencia a la tabla de AreaCodes.
Limitaciones Check y Foreign Key vs rendimiento
Puede comprobar restricciones FOREIGN KEY y mejorar el rendimiento de las consultas? No sólo nos protege contra los datos no válidos, pero al hacerlo, degrada el rendimiento? Las respuestas a estas preguntas es "Sí, se puede" y "No, no lo hacen."
Debido a que las claves foráneas y las restricciones CHECK son declarados reglas, el optimizador puede utilizar para crear planes de consulta más eficiente. Esto normalmente implica saltarse alguna parte del plan de consulta porque, por ejemplo, el Optimizador puede ver que debido a una restricción de de clave foránea, no es necesario para ejecutar esa parte del plan. El siguiente ejemplo de código es un simple ejemplo de este comportamiento con una restricción de de clave foránea. Considere las siguientes dos tablas y el FKOrdersCustomers de clave foránea:
CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY); CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NOT NULL CONSTRAINT FKOrdersCustomers REFERENCES Test.Customers (CustomerID));
Ahora, echemos un vistazo a lo que realmente hace SQL Server cuando se consulta las tablas con la clave foranea en su lugar. Para ello, en SSMS, en el menú Consulta, elija Incluir plan de ejecución real, o bien, pulse Ctrl-M.La siguiente consulta devuelve todos los pedidos que tienen una referencia válida de los clientes:
SELECT o.* FROM Test.Orders AS o WHERE EXISTS (SELECT * FROM Test.Customers AS c WHERE c.CustomerID = o.CustomerID);
El plan de ejecución que utiliza SQL Server para ejecutar esta consulta se muestra en la Figura siguiente. En el plan de ejecución, se puede ver que la tabla Test.Customers no se tiene acceso, la única tabla que se accede se Test.Orders. Esto se debe a que el optimizador sabe que no es necesario para ejecutar el operador EXISTS en esta consulta debido a la restricción de clave foránea exige que todas las órdenes para referirse a un cliente existente, que es lo que la cláusula donde los controles.
Ahora deshabilitamos la clave foránea mediante la ejecución de la siguiente declaración:
ALTER TABLE Test.Orders NOCHECK CONSTRAINT FKOrdersCustomers;
Después de ejecutar la misma consulta de nuevo, se obtiene un nuevo plan de ejecución, como se muestra en la Figura siguiente. El optimizador ejecuta el operador EXISTS (en este caso, el icono de bucles anidados en el plan de ejecución) para devolver sólo las órdenes que en realidad tienen una referencia válida a la tabla de Test.Customers. Porque desactivó la restricción de clave foránea, SQL Server no podía estar seguro de que todos los pedidos en realidad tienen referencias válidas de los clientes. Por lo tanto, tenían que ejecutar el operador EXISTS. En una tabla grande, esto puede marcar una gran diferencia en el rendimiento.
Ahora habilitamos la clave foránea mediante la ejecución de la siguiente declaración:
ALTER TABLE Test.Orders CHECK CONSTRAINT FKOrdersCustomers;
Después de ejecutar la consulta de esta vez se termina con el mismo plan una vez más, el plan se muestra en la segunda Figura. ¿Cómo puede ser esto? Que cumplió la restricción de nuevo, por lo que ahora SQL Server debe estar seguro de que todos los pedidos tienen referencias válidas de los clientes. Sin embargo, este no es el caso.
Esto se debe a la clave foránea se considera como "no confiable". El optimizador no tiene en cuenta una restricción que no es de confianza (que se aplica sólo a la clave externa y las restricciones CHECK). Su clave foránea ya no es de confianza porque, si bien se deshabilito, alguien podría haber insertado o actualizado una fila con una orden de CustomerID válido. Volviendo atras restricción de no verificar los datos existentes. Puede comprobar que la clave foránea no es de confianza al ejecutar la siguiente consulta:
SELECT name, is_not_trusted FROM sys.foreign_keys WHERE name = 'FKOrdersCustomers';
Te encuentras que la columna is_not_trusted contiene el valor 1, lo que indica que la restricción no es de confianza. Para que sea de confianza, es necesario modificar la declaración mediante la adición de la opción WITH CHECK a ella, como se muestra en el siguiente ejemplo:
ALTER TABLE Test.Orders WITH CHECK CHECK CONSTRAINT FKOrdersCustomers;
Esta opción le dice a SQL Server que verifique todas las filas en la tabla cumplan la restricción antes de encenderlo de nuevo. Si las filas no cumplen con la restricción, un mensaje de error se devuelve y la instrucción ALTER TABLE se revierte. Si se ejecuta la consulta de nuevo, te encuentras con que estás de vuelta con el plan de ejecución del primero (el que se muestra en la primer Figura) y, si se ejecuta la consulta en la vista de catálogo sys.foreign_keys otra vez, te encuentras con que la columna is_not_trusted ahora devuelve el valor 0.
La restricción es una vez más confianza.
Una última nota sobre esta implementación: puede cambiar el guión de las tablas Test.Customers y Test.Orders, como se muestra en el ejemplo siguiente, de modo que la columna CustomerID en la tabla Test.Orders permite valores NULL, es decir, que no se declara con la restricción NOT NULL:
CREATE TABLE Test.Customers ( CustomerID INT PRIMARY KEY); CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY ,CustomerID INT NULL CONSTRAINT FKOrdersCustomers REFERENCES Test.Customers (CustomerID));
Si se ejecuta la misma consulta contra este estructura de la tabla, se obtiene el plan de ejecución de la segunda Figura. Esto significa que el operador EXISTS se está ejecutando, incluso si usted tiene una restricción de confianza de clave externa en su lugar. Para persuadir a que el optimizador de volver al plan para que no ejecutamos el operador EXISTS, es necesario cambiar la consulta como se muestra en el siguiente ejemplo:
SELECT o.* FROM Test.Orders AS o WHERE EXISTS (SELECT * FROM Test.Customers AS c WHERE c.CustomerID = o.CustomerID) AND o.CustomerID IS NOT NULL;
Esto informa a SQL Server que las órdenes con valores NULL en la columna CustomerID no deben ser devueltas, lo que le trae de vuelta al plan de la primer Figura.
Resumen del capítulo
- Siempre se debe considerar que los tipos de datos que está utilizando, porque cambiar de opinión más adelante puede ser más difícil de lo que piensas.
- Considere el uso de tipos definidos por el usuario de datos para simplificar la selección del tipo de datos correcto al crear tablas y para evitar los desajustes de tipo de datos en su base de datos.
- Utilizar columnas con nombres apropiados, tal como se define en un documento de directrices para nombrar objetos y columnas, lo cual es muy importante para asegurarse de que el nombramiento en su base de datos es coherente.
- Considere la posibilidad de compresión de tablas de gran tamaño para ahorrar espacio en disco y memoria, así como un posible aumento de rendimiento.
- Implementar restricciones para verificar la integridad de datos.
- Implementar restricciones para apoyar el optimizador.
- Considere el uso de UDF en las restricciones CHECK para implementar integridad de datos avanzados.
No hay comentarios:
Publicar un comentario