W bazie danych samochodów pole kolor z tabeli samochody przyjmuje wartości kolorów jedynie ze słownika lakier. Aby połączyć tabele samochody i lakier relacją, należy zastosować kwerendę
A.
ALTER TABLE samochody ADD FOREIGN KEY (kolor) REFERENCES lakier(lakierId);
B.
ALTER TABLE samochody ADD FOREIGN KEY barwa REFERENCES samochody.lakier;
C.
ALTER TABLE samochody ADD FOREIGN KEY kolor REFERENCES lakier;
D.
ALTER TABLE lakier ADD FOREIGN KEY (barwa) REFERENCES samochody(kolor);
Pytanie z egzaminu CKE, czerwiec 2020 (arkusz E.14-SG-20.06).
Ta kwerenda dobrze oddaje sens relacji między tabelą podrzędną a tabelą słownikową: ALTER TABLE samochody ADD FOREIGN KEY (kolor) REFERENCES lakier(lakierId);. W tabeli samochody kolumna kolor pełni rolę klucza obcego, czyli przechowuje identyfikator rekordu z tabeli lakier. Tabela lakier jest tu słownikiem, a więc powinna mieć klucz główny albo przynajmniej unikalny, na przykład lakierId. Dzięki temu baza pilnuje spójności referencyjnej: nie da się wpisać w samochody takiego koloru, którego nie ma w słowniku lakier. Praktycznie wygląda to tak, że jeśli w lakier istnieje rekord lakierId = 5 oznaczający np. czerwony metalik, to samochód może mieć kolor = 5. Jeśli lakierId = 99 nie istnieje, INSERT albo UPDATE powinien zostać odrzucony. Moim zdaniem to jedna z ważniejszych rzeczy w projektowaniu baz, bo przenosi kontrolę poprawności danych z aplikacji do samego silnika bazy. Dobra praktyka branżowa to dodatkowo nadawać nazwę ograniczeniu, np. ADD CONSTRAINT fk_samochody_lakier FOREIGN KEY (kolor) REFERENCES lakier(lakierId), bo później łatwiej je usuwać, diagnozować i opisywać w dokumentacji projektu.
W tym zadaniu kluczowe jest rozumienie, która tabela jest tabelą podrzędną, a która przechowuje dane słownikowe. Samochody korzystają z wartości ze słownika lakier, więc to tabela samochody powinna dostać klucz obcy. Nie odwrotnie. Częsty błąd polega na tym, że ktoś widzi nazwę słownika i próbuje dodać ograniczenie właśnie do tabeli lakier, ale wtedy relacja zostaje odwrócona: słownik musiałby wskazywać konkretne samochody, co nie ma sensu przy typowej relacji wiele samochodów do jednego koloru. Druga sprawa to składnia SQL. W definicji klucza obcego kolumnę zwykle podaje się w nawiasach, czyli FOREIGN KEY (kolor), a po REFERENCES wskazuje się tabelę nadrzędną oraz jej kolumnę, najlepiej klucz główny lub unikalny, czyli lakier(lakierId). Sam zapis REFERENCES lakier jest zbyt nieprecyzyjny i zależny od konkretnego systemu bazodanowego, a w testach technicznych oczekuje się pełnej, jednoznacznej postaci. Problemem jest też mieszanie nazw kolumn, na przykład użycie barwa zamiast kolor, jeżeli w treści zadania podano pole kolor. Takie niedopasowanie nazw zwykle kończy się błędem wykonania zapytania albo, co gorsza, źle zaprojektowaną strukturą. Z mojego doświadczenia wynika, że uczniowie często mylą zapis tabela.kolumna z zapisem REFERENCES tabela(kolumna). W kluczu obcym nie chodzi o tekstowe połączenie nazw, tylko o formalne ograniczenie integralności referencyjnej, które silnik bazy sprawdza przy INSERT, UPDATE i czasem DELETE.