Внешние ключи TSQL на представлениях?

Если вы попытаетесь вставить , обновлять или удалять данные через представление, ограничения базовой таблицы по-прежнему применяются.

Что-то вроде этого в View2, вероятно, ваш лучший выбор:

CREATE VIEW View2
AS
     SELECT
          T2.col1,
          T2.col2,
          ...
     FROM
          Table2 T2
     INNER JOIN Table1 T1 ON
          T1.pk = T2.t1_fk

Другой подход, в зависимости от ваших требований, будет заключаться в использовании хранимой процедуры для возврата двух наборов записей. Вы передаете ей критерии фильтрации, и она использует критерии фильтрации для запроса к таблице 1, а затем эти результаты могут быть использованы для фильтрации запроса к таблице 2, чтобы его результаты также были последовательными. Затем вы возвращаете оба результата.

Мне нравится твой вопрос. Он кричит о знакомстве с Оптимизатором Запросов, и о том, как он может видеть, что некоторые соединения являются излишними, если они не служат никакой цели, или если он может упростить что-то, зная, что есть максимум одно попадание по другую сторону соединения.

Итак, большой вопрос заключается в том, можете ли вы сделать FK против CIX индексированного вида. И ответ - нет.

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

Все это работает, за исключением последнего утверждения, в котором ошибка:

Msg 1768, Level 16, State 0, Line 1
Foreign key 'FK__secondtable__id__6A325CF7' references object 'dbo.testview' which is not a user table.

Так что Foreign key должен ссылаться на пользовательскую таблицу.

Но... следующий вопрос - можно ли ссылаться на уникальный индекс, который отфильтрован в SQL 2008, чтобы получить видовой FK.

И все-таки ответ - нет.

create unique index ixUV on dbo.testtable(val) where val >= 50;
go

Это удалось.

Но теперь, если я попытаюсь создать таблицу со ссылкой на столбец val

create table dbo.thirdtable (id int identity(1,1) primary key, val int not null check (val >= 50) references dbo.testtable(val));

(Я надеялся, что ограничение проверки, которое соответствует фильтру в фильтруемом индексе, поможет системе понять, что FK должен содержать)

Но я получаю ошибку, говорящую:

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'.

Если я откажусь от фильтруемого индекса и создам нефильтрованный уникальный некластерный индекс, то я смогу создать dbo. Третий вариант без проблем.

Боюсь, что ответ все равно будет №

Данные из отфильтрованной таблицы 1 можно перевести в другую таблицу. Содержимое этой инсценировочной таблицы является вашим представлением 1, а затем вы строите представление 2, соединяя инсценировочную таблицу и таблицу 2. Таким образом, обработка для фильтрации таблицы 1 выполняется один раз и повторно используется для обоих представлений.

Действительно, представление 2 не имеет представления о том, какую фильтрацию вы выполнили в представлении 1, если только вы не скажете представление 2 о критериях фильтрации, или не сделаете его каким-то образом зависимым от результатов представления 1, что означает эмуляцию той же фильтрации, которая выполняется в представлении 1.

Ограничения не выполняют никакой фильтрации, они только предотвращают недействительные данные, или каскадно изменяют и удаляют ключи.

С чисто точки зрения целостности данных (и не имеет никакого отношения к Оптимизатору Запросов), я рассматривал Индексированное представление. Я подумал, что на нем можно сделать уникальный индекс, который может быть сломан, когда вы пытаетесь разбить целостность данных в ваших базовых таблицах.

Но.... Я не думаю, что можно достаточно хорошо обойти ограничения индексированных представлений.

Например:

Нельзя использовать внешние соединения или подзапросы. Это очень затрудняет поиск строк, которые не существуют в представлении. Если вы используете агрегаты, вы не можете использовать HAVING, так что вырезает некоторые опции, которые вы могли бы использовать и там. Вы даже не можете иметь константы в индексированном представлении, если у вас есть группировка (используете ли вы пункт GROUP BY или нет), так что вы даже не можете попробовать поместить индекс в постоянное поле, чтобы вторая строка упала. Вы не можете использовать UNION ALL, так что идея иметь счет, который разбивает уникальный индекс, когда он достигает второго нуля, не сработает.

Я чувствую, что должен быть ответ, но я боюсь, что вам придется хорошенько взглянуть на вашу реальную конструкцию и разобраться в том, что вам действительно нужно. Возможно, триггеры (и хорошие индексы) на соответствующих таблицах, так что любые изменения, которые могут что-то сломать, могут все это свернуть.

Но я действительно надеялся, что смогу предложить что-нибудь, что Оптимизатор запросов сможет использовать, чтобы помочь производительности вашей системы, но я не думаю, что смогу.

Нет, вы не можете создавать инородные ключи на видах.

Даже если бы вы могли, что бы это оставило вас? Вы все равно должны будете объявить FK после создания вида. Кто будет объявлять FK, вы или пользователь? Если пользователь достаточно опытный, чтобы объявить FK, почему он не может добавить внутреннюю ссылку на вид? Например:

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 

против:

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 

против:

против:

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 

против:

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 

против:

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 

против:

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 

против:

:

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 

Я не понимаю, как иностранный ключ упростит вашу работу.

Альтернатива:

Скопируйте подмножество данных в другую схему или базу данных. Те же таблицы, те же ключи, меньше данных, быстрее анализ, меньше споров.

Если вам нужно подмножество всех таблиц, используйте другую базу данных. Если вам нужно только подмножество из некоторых таблиц, используйте схему в той же БД. Таким образом, ваши новые таблицы все равно смогут ссылаться на не скопированные таблицы.

Тогда используйте существующие представления для копирования данных. Любые нарушения FK приведут к ошибке и выявят, какие представления требуют редактирования. При необходимости создайте задание и запланируйте его выполнение ежедневно

Мне потребовалось некоторое время, чтобы разобраться в этом недоразумении - не уверен, что я все еще понимаю полностью, но вот оно. Я буду использовать пример, близкий к вашему, но с некоторыми данными - мне проще думать в этих терминах.

Итак, первые две таблицы; A = Отдел B = Сотрудник

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 

Теперь я добавлю некоторые данные в

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 ;

Итак, теперь я создам представление для первой таблицы, чтобы отфильтровать некоторые отделы.

CREATE VIEW dbo.BlueDepartments
AS
SELECT * FROM dbo.Department
WHERE DepartmentColor = 'BLUE'
GO

Это возвращает

DepartmentID DepartmentName       DepartmentColor
------------ -------------------- ---------------
2            Engineering          BLUE

И для вашего примера я добавлю представление для второй таблицы, которая ничего не фильтрует.

CREATE VIEW dbo.AllEmployees
AS
SELECT * FROM dbo.Employee
GO

Это возвращает

EmployeeID  EmployeeName         DepartmentID
----------- -------------------- ------------
1           Lyne                 1
2           Damir                2
3           Sandy                2
4           Steve                3
5           Brian                3
6           Susan                3
7           Joe                  4

Мне кажется, что вы думаете, что Сотрудник № 5, DepartmentID = 3 указывает в никуда?

«В итоге вы получите записи в вторая точка зрения, которая ни к чему не приводит ».

Ну, это указывает на таблицу Department DepartmentID = 3 , как указано с внешним ключом. Даже если вы попытаетесь присоединиться view on view ничего не сломано:

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

Returns

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE   

Итак, здесь ничего не сломано, соединение просто не нашло совпадающих записей для DepartmentID <> 2 Это на самом деле то же самое, как если бы я объединить таблицы , а затем включить фильтр , как в первом представлении:

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

Возврат снова:

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE

В обоих случаях объединения не завершаются ошибкой, они просто работают так, как ожидалось.

Теперь я попытаюсь нарушить ссылочную целостность через представление (нет DepartmentID = 127)

INSERT  INTO dbo.AllEmployees
      ( EmployeeID, EmployeeName, DepartmentID )
VALUES( 10, 'Bob', 127 )

И это приведет к:

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'.

Если я попытаюсь удалить отдел через представление

DELETE FROM dbo.BlueDepartments
WHERE DepartmentID = 2

Что приведет к:

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'.

Таким образом, ограничения на базовые таблицы все еще применяются.

Надеюсь, это поможет, но тогда, возможно, я неправильно понял вашу проблему.