Czy istnieją DBMS, które pozwalają na klucz obcy, który odwołuje się do widoku (i nie tylko tabel podstawowych)?

Inspirowane pytaniem dotyczącym modelowania Django: Modelowanie bazy danych z wieloma relacjami wiele do wielu w Django . DB-design jest podobny do:

CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle VARCHAR(200) NOT NULL
, PRIMARY KEY (BookID)
) ;

CREATE TABLE Tag
( TagID INT NOT NULL
, TagName VARCHAR(50) NOT NULL
, PRIMARY KEY (TagID)
) ;

CREATE TABLE BookTag
( BookID INT NOT NULL
, TagID INT NOT NULL
, PRIMARY KEY (BookID, TagID)
, FOREIGN KEY (BookID)  REFERENCES Book (BookID)
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
) ;

CREATE TABLE Aspect
( AspectID INT NOT NULL
, AspectName VARCHAR(50) NOT NULL
, PRIMARY KEY (AspectID)
) ;

CREATE TABLE TagAspect
( TagID INT NOT NULL
, AspectID INT NOT NULL
, PRIMARY KEY (TagID, AspectID) 
, FOREIGN KEY (TagID)   REFERENCES Tag (TagID)
, FOREIGN KEY (AspectID)  REFERENCES Aspect (AspectID)
) ;

problem polega na tym, jak zdefiniować BookAspectRatingtabelę i wymusić integralność referencyjną, więc nie można dodać oceny dla (Book, Aspect)kombinacji, która jest nieprawidłowa.

AFAIK, złożone CHECKograniczenia (lub ASSERTIONS), które obejmują podzapytania i więcej niż jedną tabelę, które mogłyby rozwiązać ten problem, nie są dostępne w żadnym DBMS.

Innym pomysłem jest użycie (pseudokodu) widoku:

CREATE VIEW BookAspect_view
  AS
SELECT DISTINCT
    bt.BookId
  , ta.AspectId
FROM 
    BookTag AS bt
  JOIN 
    Tag AS t  ON t.TagID = bt.TagID
  JOIN 
    TagAspect AS ta  ON ta.TagID = bt.TagID 
WITH PRIMARY KEY (BookId, AspectId) ;

oraz tabelę, która ma klucz obcy do powyższego widoku:

CREATE TABLE BookAspectRating
( BookID INT NOT NULL
, AspectID INT NOT NULL
, PersonID INT NOT NULL
, Rating INT NOT NULL
, PRIMARY KEY (BookID, AspectID, PersonID)
, FOREIGN KEY (PersonID)   REFERENCES Person (PersonID)
, FOREIGN KEY (BookID, AspectID) 
    REFERENCES BookAspect_view (BookID, AspectID)
) ;

Trzy pytania:

• Czy istnieją DBMS, które pozwalają (ewentualnie zmaterializować) się VIEWz PRIMARY KEY?

• Czy istnieją DBMS, które pozwalają FOREIGN KEY, że REFERENCESjest VIEW(a nie tylko podstawy TABLE)?

• Czy problem integralności mógłby zostać rozwiązany w inny sposób - dzięki dostępnym funkcjom DBMS?

Wyjaśnienie:

Ponieważ prawdopodobnie nie ma w 100% satysfakcjonującego rozwiązania - a pytanie Django nie jest nawet moje! - Bardziej interesuje mnie ogólna strategia możliwego ataku na problem, a nie szczegółowe rozwiązanie. Tak więc odpowiedź typu „w DBMS-X można to zrobić za pomocą wyzwalaczy z tabeli A” jest całkowicie akceptowalna.

Tę regułę biznesową można wymusić w modelu przy użyciu tylko ograniczeń. Poniższa tabela powinna rozwiązać twój problem. Użyj go zamiast swojego widoku:

    CREATE TABLE BookAspectCommonTagLink
    (  BookID INT NOT NULL
    , AspectID INT NOT NULL
    , TagID INT NOT NULL
--TagID is deliberately left out of PK
    , PRIMARY KEY (BookID, AspectID)
    , FOREIGN KEY (BookID, TagID) 
        REFERENCES BookTag (BookID, TagID)
    , FOREIGN KEY (AspectID, TagID) 
        REFERENCES AspectTag (AspectID, TagID)
    ) ;

Myślę, że przekonasz się, że w wielu przypadkach złożonych reguł biznesowych nie można egzekwować za pomocą samego modelu. Jest to jeden z tych przypadków, w których, przynajmniej w SQL Server, myślę, że wyzwalacz (najlepiej zamiast wyzwalacza) lepiej służy twojemu celowi.

W Oracle jednym ze sposobów wymuszania tego rodzaju ograniczenia w sposób deklaratywny byłoby utworzenie zmaterializowanego widoku, który jest ustawiony na szybkie odświeżanie przy zatwierdzeniu, którego zapytanie identyfikuje wszystkie nieprawidłowe wiersze (tj. BookAspectRatingWiersze, które nie pasują do siebie BookAspect_view). Następnie można utworzyć trywialne ograniczenie dla zmaterializowanego widoku, które zostałoby naruszone, gdyby w zmaterializowanym widoku były wiersze. Ma to tę zaletę, że minimalizuje ilość danych, które należy powielić w zmaterializowanym widoku. Może to jednak powodować problemy, ponieważ ograniczenie jest egzekwowane tylko w miejscu, w którym dokonujesz transakcji - wiele aplikacji nie jest napisanych, aby oczekiwać, że operacja zatwierdzenia może się nie powieść - i ponieważ naruszenie ograniczenia może być nieco trudne skojarzyć z określonym wierszem lub określoną tabelą.

SIRA_PRISE pozwala na to.

Chociaż FK nie jest już nazywany „FK”, a jedynie „ograniczenie bazy danych”, a „widok” w rzeczywistości nie musi nawet być definiowany jako widok, można po prostu dołączyć wyrażenie definiujące widok do deklaracji ograniczenie bazy danych.

Twoje ograniczenie wyglądałoby mniej więcej tak

SEMIMINUS(BOOKASPECT , JOIN(BOOKTAG , TAGASPECT))

i jesteś skończony.

Jednak w większości SQL DBMS musiałbyś wykonać analizę na swoim ograniczeniu, ustalić, w jaki sposób można go złamać i zaimplementować wszystkie potrzebne wyzwalacze.

W PostgreSQL nie wyobrażam sobie rozwiązania bez angażowania wyzwalaczy, ale z pewnością można je rozwiązać w ten sposób (utrzymując jakiś zmaterializowany widok lub przed włączeniem wyzwalacza BookAspectRating). Brak kluczy obcych odwołujących się do view ( ERROR: referenced relation "v_munkalap" is not a table), nie mówiąc już o kluczu podstawowym.