Dlaczego dla sprzężeń nie są używane dopasowania klucza podstawowego / klucza obcego?

O ile mogłem się dowiedzieć, wiele DBMS (np. Mysql, postgres, mssql) używa kombinacji fk i pk tylko w celu ograniczenia zmian danych, ale rzadko są one natywnie używane do automatycznego wybierania kolumn do połączenia (tak jak naturalne łączenie robi z nazwami). Dlaczego? Jeśli już zdefiniowałeś relację między 2 tabelami za pomocą pk / fk, dlaczego baza danych nie może zrozumieć, że jeśli dołączę do tych tabel, chcę dołączyć je do kolumn pk / fk?

EDYCJA: aby to trochę wyjaśnić:

Załóżmy, że mam table1 i table2. tabela 1 ma klucz obcy w kolumnie a, która odwołuje się do klucza podstawowego w tabeli 2, kolumnie b. Teraz, jeśli dołączę do tych tabel, będę musiał zrobić coś takiego:

SELECT * FROM table1
JOIN table2 ON table1.a = table2.b

Jednak już zdefiniowałem za pomocą moich kluczy, że tabela1.a odwołuje się do tabeli2.b, więc wydaje mi się, że nie powinno być trudno, aby system DBMS automatycznie używał tabeli1.a i tabeli2.b jako kolumn łączenia, tak, że można po prostu użyć:

SELECT * FROM table1
AUTO JOIN table2

Jednak wiele DBMS nie wydaje się implementować czegoś takiego.

W wielu przypadkach istnieje więcej niż jeden sposób na połączenie dwóch tabel; Zobacz inne odpowiedzi, aby uzyskać wiele przykładów. Oczywiście można powiedzieć, że użycie „automatycznego łączenia” byłoby błędem w takich przypadkach. Wtedy pozostanie tylko garść prostych przypadków, w których można go użyć.

Istnieje jednak poważna wada! Zapytania, które są dziś poprawne, jutro mogą stać się błędem, dodając drugi FK do tej samej tabeli!

Powiem to jeszcze raz: dodając kolumny, zapytania, które nie używają tych kolumn, mogą zmienić się z „poprawnego” w „błąd”!

To taki koszmar konserwacji, że jakikolwiek rozsądny przewodnik po stylu zabroniłby korzystania z tej funkcji. Większość już zakazuje select *z tego samego powodu!

Wszystko to byłoby do zaakceptowania, gdyby poprawiono wydajność. Tak jednak nie jest.

Podsumowując, ta funkcja może być używana tylko w ograniczonym zestawie prostych przypadków, nie zwiększa wydajności, a większość przewodników po stylach i tak zabroniłaby jej używania.

Nic więc dziwnego, że większość dostawców baz danych decyduje się poświęcić czas na ważniejsze rzeczy.

Klucz obcy ma na celu ograniczenie danych. tj. egzekwować integralność referencyjną. Otóż ​​to. Nic więcej.

1. Możesz mieć wiele kluczy obcych do tej samej tabeli. Zastanów się, gdzie przesyłka ma punkt początkowy i końcowy.

   table: USA_States
   StateID
   StateName
   
   table: Shipment
   ShipmentID
   PickupStateID Foreign key
   DeliveryStateID Foreign key

   Możesz dołączyć do grupy na podstawie stanu odbioru. Może chcesz dołączyć do stanu dostawy. Może chcesz wykonać 2 łączenia dla obu! Silnik SQL nie ma możliwości dowiedzenia się, czego chcesz.

2. Często krzyżujesz wartości skalarne. Chociaż skalary są zwykle wynikiem pośrednich obliczeń, czasami masz specjalną tabelę z dokładnie 1 rekordem. Gdyby silnik próbował wykryć klucz foriegn dla łączenia ... nie miałoby to sensu, ponieważ połączenia krzyżowe nigdy nie pasują do kolumny.

3. W niektórych szczególnych przypadkach dołączasz do kolumn, z których żadna nie jest unikalna. Dlatego obecność PK / FK na tych kolumnach jest niemożliwa.

4. Możesz myśleć, pkt 2 i 3 powyżej nie są istotne, ponieważ wasze pytania o to, kiedy tam JEST jeden PK / FK relacji między tabelami. Jednak obecność pojedynczego PK / FK między tabelami nie oznacza, że ​​nie możesz mieć innych pól do przyłączenia oprócz PK / FK. Mechanizm SQL nie będzie wiedział, na których polach chcesz dołączyć.

5. Powiedzmy, że masz tabelę „USA_States” i 5 innych tabel z FK do stanów. Tabele „pięć” mają także kilka kluczy obcych. Czy silnik SQL powinien automatycznie łączyć tabele „pięciu” z „USA_States”? A może powinien połączyć ze sobą „piątkę”? Obie? Można skonfigurować relacje tak, aby silnik sql wszedł w nieskończoną pętlę, próbując połączyć elementy razem. W tej sytuacji niemożliwe jest, aby silnik SQL zgadł, co chcesz.

Podsumowując: PK / FK nie ma nic wspólnego z łączeniami tabel. Są to osobne, niepowiązane ze sobą rzeczy. To tylko przypadkowy wypadek, do którego często dołączasz na kolumnach PK / FK.

Czy chcesz, aby silnik SQL zgadywał, czy jest to pełne, lewe, prawe czy wewnętrzne połączenie? Nie wydaje mi się Chociaż byłby to prawdopodobnie mniejszy grzech niż odgadywanie kolumn, które należy dołączyć.

koncepcja „łączności”. Relacje r1i r2łączenie jest możliwe tylko wtedy, gdy atrybuty o tej samej nazwie są tego samego typu ... koncepcja ta dotyczy nie tylko łączenia jako takiego, ale także różnych innych operacji [takich jak unia].

Teoria SQL i relacyjna: jak pisać dokładny kod SQL według daty CJ

Standardowy SQL ma już taką funkcję, znaną jako NATURAL JOIN, i został zaimplementowany w mySQL.

Chociaż twoja sugestia nie jest tak godna, wydaje się rozsądna. W przypadku SQL Server (który nie obsługujeNATURAL JOIN ) używam INNER JOINPytań SQL w Management Studio: podczas pisania InteliSense sugeruje ONklauzule oparte na wspólnych nazwach atrybutów i kluczach obcych i uważam, że jest to bardzo przydatne. Nie chcę jednak widzieć nowego (standardowego) typu złączenia SQL do tego celu.

SQL był pierwszy!

Klucze obce i ograniczenia dotyczące klucza obcego pojawiły się później i są zasadniczo optymalizacją dla aplikacji w stylu „transakcyjnym”.

Relacyjne bazy danych zostały pierwotnie pomyślane jako metoda stosowania złożonych zapytań na zestawach danych w sposób, który można matematycznie udowodnić za pomocą algebry relacyjnej. IE dla danego zestawu danych i danego zapytania zawsze istnieje jedna poprawna odpowiedź.

Od tego czasu relacyjne bazy danych przeszły długą drogę, a podstawowym zastosowaniem jako warstwy trwałości dla systemów transakcyjnych nie było CODD i in. wszystko przewidziane.

Jednak organ normalizacyjny ANSI w odniesieniu do wszystkich sprzecznych celów i polityki dostawcy zawsze starał się zachować „matematycznie możliwe” właściwości SQL.

Jeśli pozwolisz, aby baza danych wnioskowała o właściwościach łączenia z „ukrytych” danych kluczy obcych, utracisz tę właściwość (rozważ dwuznaczność, jeśli zdefiniowano więcej niż jeden zestaw kluczy obcych).

Również programista czytający SQL niekoniecznie wiedziałby, jakie klucze obce zostały obecnie zdefiniowane dla dwóch tabel, i musiałby zbadać schemat bazy danych, aby ustalić, co robi zapytanie.

Chociaż zdefiniowałeś relację klucza obcego, nie znaczy to, że chcesz dołączyć do tabel we wszystkich zapytaniach. Jest to najbardziej prawdopodobna metoda łączenia tabel, ale zdarzają się przypadki, w których jest to nieprawidłowe.

• Możesz do pewnego celu użyć kartezjańskiego produktu z dwóch tabel lub ich części.
• Mogą istnieć inne pola, do których możesz dołączyć w innym celu.
• Jeśli łączysz trzy lub więcej tabel, jedna z tabel może być powiązana z dwiema lub więcej tabelami. W takim przypadku zwykle tylko jedna z możliwych relacji FK może być odpowiednia w zapytaniu.

Możesz działać na fałszywych założeniach. Mówisz „o ile możesz się dowiedzieć”, ale nie podajesz żadnych dowodów empirycznych ani dowodowych. Jeśli pk lub fk są najlepszym indeksem dla zapytania, zostanie użyte. Nie wiem, dlaczego to widzisz, ale sądzę, że źle sformułowane zapytania.

Edytuj teraz, gdy pytanie zostało całkowicie przepisane: opisany przypadek dotyczyłby tylko bardzo małego zestawu zapytań. Co się stanie, jeśli dołączy 12 tabel? Co, jeśli nie ma FK-ów… Nawet jeśli domyślnie było włączone łączenie, nadal zawsze określałem łączenie tylko dla czytelności. (Nie chcę patrzeć na dane, a następnie próbować dowiedzieć się, co się łączy)

Niektóre narzędzia do wysyłania zapytań faktycznie wykonują automatyczne łączenie, a następnie umożliwiają usunięcie lub edycję połączenia. Myślę, że robi to Konstruktor kwerend MS Access.

Wreszcie standard ANSII stanowi, że należy podać sprzężenie. To wystarczający powód, aby na to nie pozwolić.

Istnieje wiele powodów, dla których baza danych nie może tego bezpiecznie zrobić, w tym fakt, że dodanie / usunięcie kluczy obcych zmieni znaczenie wcześniej napisanych zapytań, w tym zapytań w kodzie źródłowym aplikacji. Większość baz danych również nie ma dobrego zestawu kluczy obcych, które obejmują wszystkie możliwe sprzężenia, które prawdopodobnie chcesz zrobić. Klucze obce są często usuwane w celu przyspieszenia działania systemów i nie można ich używać w tabelach, które są ładowane w „niewłaściwej” kolejności z pliku.

Jednak nie ma powodu, dla którego narzędzie do projektowania zapytań lub edytor tekstu nie może automatycznie dokończyć łączenia za pomocą kluczy obcych w taki sam sposób, w jaki dają inteligencję na temat nazwy kolumny. Możesz edytować zapytanie, jeśli narzędzie go pomyliło, i zapisać całkowicie zdefiniowane zapytanie. Takie narzędzie mogłoby również użytecznie wykorzystać konwencję nazewnictwa kolumn kluczy obcych nazwą tabeli „nadrzędnej” i kolumn o tej samej nazwie w tabeli nadrzędnej / podrzędnej itp.

(Moja żona nadal nie rozumie różnicy między Management Studio a Sql Server i mówi o uruchomieniu serwera sql, kiedy zaczyna studio zarządzania!)

Naturalne łączenie „automatycznie” łączy się na równych wspólnych kolumnach, ale powinieneś napisać, że jeśli tego właśnie chcesz, na podstawie znaczeń tabeli i pożądanego wyniku. Nie ma „automatycznej” wiedzy o tym, jak należy połączyć dwie tabele lub w jakikolwiek inny sposób jakakolwiek tabela „powinna” pojawić się w zapytaniu. Nie musimy znać ograniczeń zapytań. Ich obecność oznacza po prostu, że dane wejściowe mogą być ograniczone, a tym samym dane wyjściowe mogą być również. Możesz zdefiniować pewnego rodzaju operatora join_on_fk_to_pk, który „automatycznie” łączy według zadeklarowanych ograniczeń; ale jeśli chcesz, aby znaczenie zapytania pozostało takie samo, jeśli zmienią się tylko ograniczenia, ale nie znaczenie w tabeli, musisz zmienić to zapytanie, aby nie używać nowych deklarowanych wiązań.już pozostawia to samo znaczenie pomimo zmian ograniczeń .

Jakie ograniczenia obowiązują (w tym PK, FK, UNIKALNE i SPRAWDZANIE) nie wpływają na znaczenie tabel. Oczywiście, jeśli znaczenie tabeli się zmieni, wówczas przeciwności mogą się zmienić. Ale jeśli ograniczenia się zmienią, nie oznacza to, że zapytania powinny się zmienić.

Nie trzeba znać ograniczeń dotyczących zapytań. Wiedza o ograniczeniach oznacza, że ​​możemy użyć dalszych wyrażeń, które bez ograniczenia nie zwrócą tej samej odpowiedzi. Np. Oczekiwanie przez UNIQUE, że tabela ma jeden wiersz, więc możemy go użyć jako skalara. Te zapytania mogą zostać przerwane, jeśli ograniczenie zostało przyjęte, ale nie zadeklarowane. Ale oświadczenie, że zapytanie nie zakłada, że ​​nie może go złamać.

Czy istnieje jakaś reguła praktyczna przy tworzeniu zapytania SQL z czytelnego dla człowieka opisu?

Powodem jest to, że istnieje JĘZYK, a następnie istnieją podstawowe zasady. Język jest rzadki i brakuje w nim wielu funkcji, których można by oczekiwać w języku ogólnego przeznaczenia. To po prostu fajna funkcja, która nie została dodana do języka i prawdopodobnie nie będzie. To nie jest martwy język, więc jest jakaś nadzieja, ale nie byłbym optymistą.

Jak zauważyli inni, niektóre implementacje używają rozszerzenia, w którym join (kolumna) łączy dwie tabele na podstawie wspólnej nazwy kolumny, która jest nieco podobna. Ale nie jest szeroko rozpowszechniony. Pamiętaj, że to rozszerzenie różni się odSELECT * FROM employee NATURAL JOIN department; składni, która nie zawiera sposobu określania, których kolumn użyć. Ani też nie polegaj na relacji między tabelami, co czyni je niewiarygodnymi (naturalna składnia łączenia bardziej niż rozszerzenie).

Nie ma fundamentalnej przeszkody dla „wewnętrznej tabeli łączenia w PKFK”, gdzie PKFK jest słowem kluczowym oznaczającym „relację klucza obcego zdefiniowaną między dwiema tabelami”, mogą występować problemy z wieloma fk do tej samej tabeli, ale może to po prostu spowodować błąd. Pytanie brzmi: czy osoby projektujące język uważają, że a) dobry pomysł ib) lepiej nad nim pracować niż jakaś inna zmiana języka ...

Jeśli zakłada się, że pominięcie klauzuli ON jest zgodne z polami opartymi na integralności referencyjnej, jak zrobiłbyś produkt kartezjański?

Edycja: za pomocą AUTO Zaletami tego jest trochę mniej pisania na klawiaturze i nie musisz wiedzieć, jak są połączone, ani pamiętać skomplikowanego łączenia. Jeśli relacja zmienia się, jest obsługiwana automatycznie, ale rzadko się to zdarza, z wyjątkiem wczesnego rozwoju.

To, co musisz teraz zrobić, to zdecydować, czy wszystkie twoje połączenia AUTO utrzymają się podczas zmiany relacji, aby pasowały do ​​intencji Twojej instrukcji select.

dlaczego baza danych nie może zrozumieć, że jeśli dołączę do tych tabel, chcę je dołączyć w kolumnach pk / fk?

Częściami tego są:

1 - teoretycznie możesz łączyć tabele na dowolnych kolumnach z dwóch tabel. Chociaż nie jest to powszechna praktyka, jest ona prawidłowa. Pamiętaj, że SQL jest jak język programowania, nie rozumie, jakie informacje znajdują się w kolumnach oczywiście i nazwy, dla SQL, niewiele to znaczy.

2 - Istnieją różne rodzaje połączeń (lewy, prawy, wewnętrzny) - Połączenia wewnętrzne to tylko 1 z nich.

3 - Standard SQL może kierować się zasadą bycia językiem niższego poziomu, który pozwala dialektom wyższego poziomu tworzyć z niego inteligencję. Porównanie jest nieco jaśniejsze, jeśli myślisz o języku czwartej generacji w porównaniu do języka trzeciej generacji. W rzeczywistości jedno narzędzie, którego użyłem, IEF, pozwoliło napisać coś takiego:

ReadEach Customer 
Where Customer Places Orders and That Customer LivesIn "California" 
and OrderValue > 100.00

Podsumowując, twoja sugestia jest interesująca i może zostać zaimplementowana albo jako część standardu, albo jako procedura składowana (domyślnie byłoby to połączenie wewnętrzne).

Tiddo, uważam, że masz całkowitą rację, SQL na ten temat jest dość głupi , i pamiętam, że pomyślałem o tym, co zrobiłeś o obcych kluczach podczas nauki SQL dziesięć lat temu.

Ok, biorąc pod uwagę to, w końcu musiałem zdać ten egzamin; i żeby to przekazać, musiałem odpuścić . SQL jest bardziej katastrofą, niż ktokolwiek może się przyznać, jego ścieżka standaryzacji jest całkowitą katastrofą, a niektóre implementacje są groźne . Ogólnie jest to jednak całkiem przydatne. (Nie jestem luddite K / V)

W takim razie klucze obce ... wcale nie są tak przydatne. Są ważną koncepcją w modelu relacyjnym , ok, ale funkcja SQL o tej samej nazwie nie jest dobrze porównywana.

Powiem ci wprost: nie używać tej funkcji o nazwie SQL Foreign Keyw ogóle , aż trafisz jakiś wielki system z problemów z wydajnością. Jawne mówienie silnikowi, które pole jest kluczem obcym, a który nie służy wyłącznie do indeksowania i jest niewidoczne dla użytkownika db.

Czy to jest mylące?
Tak.

Czy zamierzają uczynić go silniejszym teraz, po 30 latach wprowadzania w błąd?
Nie ma mowy.

Całkowicie ignoruję klucze obce, dopóki nie będzie to konieczne ... naprawiłem SQL dla mnie?
Tak!

I dlaczego, do cholery, to wszystko się wydarzyło?
Cóż, funkcja, którą nazywamy kluczami obcymi, została później dodana do SQL; SQL jest standardem, który ewoluował z czasem, oddolnie. Sprzedawcy wdrożyli absurdalne funkcje, a standardowe ciała dostosowano.

Klucze obce, jak powiedziano, gdzie służyły tylko do indeksowania i nie było dostępnej konstrukcji JOIN. (dołącza gdzie odbywa się z SELECTzapytaniami, JOINpytania są dość niedawno i ma na celu jedynie alias SELECTfunkcjonalności) Prawdopodobnie jednak, że wywołanie tej flagi indeksowania FOREIGN KEY, był sprytny nazewnictwa-Hack nad pojęciami teorii relacyjnych db.