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

У меня есть база данных SQL-Server 2008 и схема, которая использует ограничения внешнего ключа для обеспечения ссылочной целостности. Работает по назначению. Теперь пользователь создает представления исходных таблиц для работы только с подмножествами данных. Моя проблема заключается в том, что фильтрация определенных наборов данных в некоторых таблицах, но не в других, будет нарушать ограничения внешнего ключа.
Представьте себе две таблицы "один" и "два". "один" содержит только столбец id со значениями 1,2,3. "Два" ссылки "один". Теперь вы создаете представления для обеих таблиц. Представление для таблицы "два" не фильтрует ничего, в то время как представление для таблицы "один" удаляет все строки, кроме первого. В итоге вы получите записи во втором представлении, в котором нет нигде.

Есть ли способ избежать этого? Можете ли вы иметь ограничения внешнего ключа между представлениями?

Некоторые разъяснения в ответ на некоторые комментарии:
Я знаю, что основные ограничения будут обеспечивать целостность данных даже при вставке через представления. Моя проблема заключается в заявлениях, которые потребляют взгляды. Эти утверждения были написаны с учетом оригинальных таблиц и предполагают, что некоторые объединения не могут потерпеть неудачу. Это предположение всегда справедливо при работе с таблицами, но просмотры потенциально могут его нарушить.
Объединение/проверка всех ограничений при создании представлений в первую очередь аннуируется из-за большого количества ссылочных таблиц. Таким образом, я надеялся избежать этого.

Ответ 1

Питер уже наткнулся на это, но лучшим решением будет:

Создайте "основную" логику (которая фильтрует ссылочную таблицу) один раз.
Все представления связанных таблиц присоединяются к представлению, созданному для (1), а не к исходной таблице.

т.е.

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)

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

Ответ 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.

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

Но... следующий вопрос о том, можно ли ссылаться на уникальный индекс, который был отфильтрован в 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.thirdtable без каких-либо проблем.

Итак, я боюсь, что ответ по-прежнему кажется Нет.

Ответ 3

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

Итак, первые две таблицы; 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

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

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

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

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

Возвращает

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

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

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

Ответ 4

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

Ответ 5

Что-то вроде этого в View2, вероятно, лучше всего:

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

Ответ 6

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

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

Это было бы дорого по многим причинам Ссылочную целостность в таблице поиска следует применять с помощью триггеров. Дополнительное хранение таблицы поиска и индексация в дополнение к таблицам данных. Чтение данных почти наверняка будет включать хранимую процедуру или три для выполнения фильтрованного UNION. Оценка плана запроса также будет иметь затраты на разработку.

Список продолжается, но он может работать в некоторых сценариях.

Ответ 7

Использование схемы Роба Фарли:

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

Мне пришлось создать крошечную вспомогательную таблицу (dbo.z), чтобы сделать эту работу, потому что индексированные представления не могут соединяться самостоятельно, внешние соединения, подзапросы или производные таблицы (и TVCs считаются производными таблицами).

Ответ 8

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

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

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

Ответ 9

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

Ответ 10

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

Даже если бы вы могли, где это оставило бы вас? Вам все равно придется объявлять 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

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

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

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

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

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

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

Ответ 11

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

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

Например:

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

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

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