tablas - TSQL claves foráneas en las vistas?
poner llaves foraneas sql (11)
Tengo una base de datos SQL-Server 2008 y un esquema que usa restricciones de clave externa para imponer la integridad referencial. Funciona según lo previsto. Ahora el usuario crea vistas en las tablas originales para trabajar solo en subconjuntos de los datos. Mi problema es que el filtrado de ciertos conjuntos de datos en algunas tablas pero no en otras violará las restricciones de clave externa.
Imagina dos tablas "uno" y "dos". "one" contiene solo una columna id con valores 1,2,3. "Dos" referencias "uno". Ahora creas vistas en ambas tablas. La vista para la tabla "dos" no filtra nada mientras que la vista para la tabla "uno" elimina todas las filas excepto la primera. Terminarás con entradas en la segunda vista que no apuntan a ninguna parte.
Hay alguna manera de evitar esto? ¿Puede tener restricciones de clave externa entre vistas?
Algunas aclaraciones en respuesta a algunos de los comentarios:
Soy consciente de que las restricciones subyacentes garantizarán la integridad de los datos incluso cuando se insertan a través de las vistas. Mi problema radica en las declaraciones que consumen los puntos de vista. Esas declaraciones se han escrito teniendo en cuenta las tablas originales y se supone que ciertas uniones no pueden fallar. Esta suposición siempre es válida cuando se trabaja con las tablas, pero las vistas pueden romperla.
La combinación / comprobación de todas las restricciones al crear las vistas en primer lugar es análoga debido a la gran cantidad de tablas de referencia. Por eso esperaba evitar eso.
Algo como esto en View2 es probablemente tu mejor apuesta:
CREATE VIEW View2
AS
SELECT
T2.col1,
T2.col2,
...
FROM
Table2 T2
INNER JOIN Table1 T1 ON
T1.pk = T2.t1_fk
Desde una perspectiva puramente de integridad de datos (y nada que ver con el Optimizador de consultas), había considerado una vista indizada. Pensé que podrías hacer un índice único en él, que podría romperse cuando intentas tener integridad rota en tus tablas subyacentes.
Pero ... no creo que puedas superar las restricciones de las vistas indexadas lo suficientemente bien.
Por ejemplo:
No se pueden usar combinaciones externas o consultas secundarias. Eso hace que sea muy difícil encontrar las filas que no existen en la vista. Si usa agregados, no puede usar HAVING, por lo que eso elimina algunas opciones que podría usar allí también. Ni siquiera puede tener constantes en una vista indizada si tiene agrupación (use o no una cláusula GROUP BY), por lo que ni siquiera puede intentar colocar un índice en un campo constante para que se caiga una segunda fila. No puede usar UNION ALL, por lo que la idea de tener un recuento que rompa un índice único cuando llegue a un segundo cero no funcionará.
Siento que debería haber una respuesta, pero me temo que tendrá que echar un buen vistazo a su diseño real y resolver lo que realmente necesita. Tal vez los disparadores (y los buenos índices) en las tablas involucradas, de modo que cualquier cambio que pueda romper algo pueda rodar todo eso.
Pero realmente esperaba poder sugerir algo que el Optimizador de consultas pudiera aprovechar para ayudar al rendimiento de su sistema, pero no creo que pueda.
Me encanta tu pregunta Grita de familiaridad con el Optimizador de consultas, y cómo puede ver que algunas combinaciones son redundantes si no sirven para nada, o si pueden simplificar algo sabiendo que hay como máximo un golpe en el otro lado de una combinación.
Entonces, la gran pregunta es si se puede hacer un FK contra el CIX de una Vista Indizada. Y la respuesta es no.
create table dbo.testtable (id int identity(1,1) primary key, val int not null);
go
create view dbo.testview with schemabinding as
select id, val
from dbo.testtable
where val >= 50
;
go
insert dbo.testtable
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70
go
create unique clustered index ixV on dbo.testview(id);
go
create table dbo.secondtable (id int references dbo.testview(id));
go
Todo esto funciona a excepción de la última declaración, que falla con:
Msg 1768, Level 16, State 0, Line 1
Foreign key ''FK__secondtable__id__6A325CF7'' references object ''dbo.testview'' which is not a user table.
Así que la clave externa debe hacer referencia a una tabla de usuario.
Pero ... la siguiente pregunta es sobre si podría hacer referencia a un índice único que se filtra en SQL 2008, para lograr un FK de vista.
Y aún así la respuesta es no.
create unique index ixUV on dbo.testtable(val) where val >= 50;
go
Esto tuvo éxito.
Pero ahora si intento crear una tabla que haga referencia a la columna val
create table dbo.thirdtable (id int identity(1,1) primary key, val int not null check (val >= 50) references dbo.testtable(val));
(Esperaba que la restricción de verificación que coincidía con el filtro en el índice filtrado pudiera ayudar al sistema a comprender que el FK debería mantenerse)
Pero me sale un error diciendo:
There are no primary or candidate keys in the referenced table ''dbo.testtable'' that matching the referencing column list in the foreign key ''FK__thirdtable__val__0EA330E9''.
Si suelto el índice filtrado y creo un índice único no agrupado no filtrado, puedo crear dbo.thirdtable sin ningún problema.
Así que me temo que la respuesta todavía parece ser No.
Me tomó algo de tiempo descubrir el malentendido aquí - no estoy seguro de si todavía lo entiendo completamente, pero aquí está. Usaré un ejemplo cercano al tuyo, pero con algunos datos, más fácil de pensar en estos términos.
Así que las dos primeras tablas; A = Departamento B = Empleado
CREATE TABLE Department
(
DepartmentID int PRIMARY KEY
,DepartmentName varchar(20)
,DepartmentColor varchar(10)
)
GO
CREATE TABLE Employee
(
EmployeeID int PRIMARY KEY
,EmployeeName varchar(20)
,DepartmentID int FOREIGN KEY REFERENCES Department ( DepartmentID )
)
GO
Ahora voy a tirar algunos datos en
INSERT INTO Department
( DepartmentID, DepartmentName, DepartmentColor )
SELECT 1, ''Accounting'', ''RED'' UNION
SELECT 2, ''Engineering'', ''BLUE'' UNION
SELECT 3, ''Sales'', ''YELLOW'' UNION
SELECT 4, ''Marketing'', ''GREEN'' ;
INSERT INTO Employee
( EmployeeID, EmployeeName, DepartmentID )
SELECT 1, ''Lyne'', 1 UNION
SELECT 2, ''Damir'', 2 UNION
SELECT 3, ''Sandy'', 2 UNION
SELECT 4, ''Steve'', 3 UNION
SELECT 5, ''Brian'', 3 UNION
SELECT 6, ''Susan'', 3 UNION
SELECT 7, ''Joe'', 4 ;
Entonces, ahora crearé una vista en la primera tabla para filtrar algunos departamentos.
CREATE VIEW dbo.BlueDepartments
AS
SELECT * FROM dbo.Department
WHERE DepartmentColor = ''BLUE''
GO
Esto devuelve
DepartmentID DepartmentName DepartmentColor
------------ -------------------- ---------------
2 Engineering BLUE
Y según su ejemplo, agregaré una vista para la segunda tabla que no filtra nada.
CREATE VIEW dbo.AllEmployees
AS
SELECT * FROM dbo.Employee
GO
Esto devuelve
EmployeeID EmployeeName DepartmentID
----------- -------------------- ------------
1 Lyne 1
2 Damir 2
3 Sandy 2
4 Steve 3
5 Brian 3
6 Susan 3
7 Joe 4
Me parece que usted piensa que el empleado n. ° 5, DepartmentID = 3 puntos a ninguna parte?
"Terminarás con entradas en la segunda vista que no apuntan a ninguna parte".
Bueno, apunta a la tabla del DepartmentID = 3 , como se especifica con la clave externa. Incluso si intentas unir la vista en la vista, nada está roto:
SELECT e.EmployeeID
,e.EmployeeName
,d.DepartmentID
,d.DepartmentName
,d.DepartmentColor
FROM dbo.AllEmployees AS e
JOIN dbo.BlueDepartments AS d ON d.DepartmentID = e.DepartmentID
ORDER BY e.EmployeeID
Devoluciones
EmployeeID EmployeeName DepartmentID DepartmentName DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2 Damir 2 Engineering BLUE
3 Sandy 2 Engineering BLUE
Por lo tanto, aquí no se rompe nada, la unión simplemente no encontró registros coincidentes para DepartmentID <> 2 Esto es en realidad como si uniera tablas y luego incluyera el filtro como en la primera vista:
SELECT e.EmployeeID
,e.EmployeeName
,d.DepartmentID
,d.DepartmentName
,d.DepartmentColor
FROM dbo.Employee AS e
JOIN dbo.Department AS d ON d.DepartmentID = e.DepartmentID
WHERE d.DepartmentColor = ''BLUE''
ORDER BY e.EmployeeID
Vuelve de nuevo:
EmployeeID EmployeeName DepartmentID DepartmentName DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2 Damir 2 Engineering BLUE
3 Sandy 2 Engineering BLUE
En ambos casos, las uniones no fallan, simplemente hacen lo que se espera.
Ahora intentaré romper la integridad referencial a través de una vista (no hay un ID de departamento = 127)
INSERT INTO dbo.AllEmployees
( EmployeeID, EmployeeName, DepartmentID )
VALUES( 10, ''Bob'', 127 )
Y esto resulta en:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Department", column ''DepartmentID''.
Si intento borrar un departamento a través de la vista
DELETE FROM dbo.BlueDepartments
WHERE DepartmentID = 2
Lo que resulta en:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Employee", column ''DepartmentID''.
Así que las restricciones en las tablas subyacentes siguen siendo aplicables.
Espero que esto ayude, pero entonces tal vez entendí mal tu problema.
No, no puedes crear claves externas en las vistas.
Incluso si pudieras, ¿dónde te dejaría eso? Aún tendría que declarar el FK después de crear la vista. ¿Quién declararía el FK, usted o el usuario? Si el usuario es lo suficientemente sofisticado como para declarar un FK, ¿por qué no pudo agregar una unión interna a la vista de referencia? p.ej:
create view1 as select a, b, c, d from table1 where a in (1, 2, 3)
go
create view2 as select a, m, n, o from table2 where a in (select a from view1)
go
vs:
create view1 as select a, b, c, d from table1 where a in (1, 2, 3)
go
create view2 as select a, m, n, o from table2
--# pseudo-syntax for fk:
alter view2 add foreign key (a) references view1 (a)
go
No veo cómo la clave externa simplificaría su trabajo.
Alternativamente:
Copie el subconjunto de datos en otro esquema o base de datos. Las mismas tablas, las mismas claves, menos datos, análisis más rápidos, menos contención.
Si necesita un subconjunto de todas las tablas, use otra base de datos. Si solo necesita un subconjunto de algunas tablas, use un esquema en la misma base de datos. De esa manera, sus nuevas tablas aún pueden hacer referencia a las tablas no copiadas.
Luego usa las vistas existentes para copiar los datos. Cualquier violación de FK generará un error e identificará qué vistas requieren edición. Crear un trabajo y programarlo diariamente, si es necesario.
Otro enfoque, según sus requisitos, sería utilizar un procedimiento almacenado para devolver dos conjuntos de registros. Pasa los criterios de filtrado y utiliza los criterios de filtrado para consultar la tabla 1, y luego esos resultados se pueden usar para filtrar la consulta a la tabla 2 para que los resultados también sean consistentes. Entonces devuelves ambos resultados.
Peter ya dio con esto, pero la mejor solución es:
- Cree la lógica "principal" (que filtra la tabla a la que se hace referencia) una vez.
- Haga que todas las vistas en tablas relacionadas se unan a la vista creada para (1), no a la tabla original.
Es decir,
CREATE VIEW v1 AS SELECT * FROM table1 WHERE blah
CREATE VIEW v2 AS SELECT * FROM table2 WHERE EXISTS
(SELECT NULL FROM v1 WHERE v1.id = table2.FKtoTable1)
Claro, el azúcar sintáctico para propagar filtros para vistas en una tabla a vistas en tablas subordinadas sería útil, pero, por desgracia, no es parte del estándar SQL. Dicho esto, esta solución sigue siendo lo suficientemente buena: eficiente, directa, fácil de mantener y garantiza el estado deseado para el código consumidor.
Podría colocar los datos de la tabla 1 filtrada en otra tabla. El contenido de esta tabla de etapas es su vista 1, y luego construye la vista 2 a través de una combinación de la tabla de etapas y la tabla 2. De esta manera, el proceso para filtrar la tabla 1 se realiza una vez y se reutiliza para ambas vistas.
Realmente lo que se reduce a es que la vista 2 no tiene idea de qué tipo de filtrado realizó en la vista 1, a menos que le diga a la vista 2 los criterios de filtrado, o lo haga de alguna manera dependiente de los resultados de la vista 1, lo que significa emular el mismo filtrado eso ocurre en view1.
Las restricciones no realizan ningún tipo de filtrado, solo evitan datos no válidos, o cambios y eliminaciones de claves en cascada.
Si intenta insertar, actualizar o eliminar datos a través de una vista, las restricciones de la tabla subyacente aún se aplican.
Si se desplaza sobre las tablas para que las columnas de Identidad no entren en conflicto, una posibilidad sería utilizar una tabla de búsqueda que haga referencia a las diferentes tablas de datos por Identidad y una referencia de tabla.
Las claves externas en esta tabla trabajarían en la línea para las tablas de referencia.
Esto sería costoso en una serie de formas en que la integridad referencial en la tabla de búsqueda tendría que ser aplicada utilizando los desencadenantes. Almacenamiento adicional de la tabla de búsqueda e indexación además de las tablas de datos. La lectura de los datos implicaría casi seguramente un procedimiento almacenado o tres para ejecutar un UNION filtrado. La evaluación del plan de consultas también tendría un costo de desarrollo.
La lista continúa, pero podría funcionar en algunos escenarios.
Usando el esquema de Rob Farley:
CREATE TABLE dbo.testtable(
id int IDENTITY(1,1) PRIMARY KEY,
val int NOT NULL);
go
INSERT dbo.testtable(val)
VALUES(20),(30),(40),(50),(60),(70);
go
CREATE TABLE dbo.secondtable(
id int NOT NULL,
CONSTRAINT FK_SecondTable FOREIGN KEY(id) REFERENCES dbo.TestTable(id));
go
CREATE TABLE z(n tinyint PRIMARY KEY);
INSERT z(n)
VALUES(0),(1);
go
CREATE VIEW dbo.SecondTableCheck WITH SCHEMABINDING AS
SELECT 1 n
FROM dbo.TestTable AS t JOIN dbo.SecondTable AS s ON t.Id = s.Id
CROSS JOIN dbo.z
WHERE t.Val < 50;
go
CREATE UNIQUE CLUSTERED INDEX NoSmallIds ON dbo.SecondTableCheck(n);
go
Tuve que crear una pequeña tabla auxiliar (dbo.z) para hacer que esto funcionara, porque las vistas indizadas no pueden tener uniones personales, combinaciones externas, subconsultas o tablas derivadas (y las TVC cuentan como tablas derivadas).