5 ograniczeń SQL gwarantujących dobrą strukturę bazy danych
Kluczowe wnioski
Stosowanie ograniczeń SQL ma kluczowe znaczenie dla zachowania integralności bazy danych, zapewniając jednocześnie spójność jej zawartości. Jednym z takich ograniczeń jest ograniczenie NOT NULL, które narzuca, że kolumny nie mogą akceptować wartości null, tym samym wymuszając znaczenie dostarczania prawidłowych informacji w tych polach.
Aby zachować integralność i dokładność danych w relacyjnej bazie danych, ważne jest, aby zaimplementować ograniczenia klucza głównego dla określonych kolumn lub zestawów kolumn. Te unikalne identyfikatory zapobiegają powielaniu rekordów i promują wydajną organizację i wyszukiwanie informacji.
Ograniczenia klucza obcego służą do utrzymania integralności relacyjnych baz danych poprzez egzekwowanie integralności referencyjnej, zapewniając, że każdy rekord w tabeli podrzędnej odpowiada istniejącemu rekordowi w tabeli nadrzędnej. Ograniczenia te są niezbędne do zachowania wzajemnych powiązań danych w wielu tabelach i zapobiegania nieautoryzowanym modyfikacjom lub usunięciom, które mogłyby zagrozić dokładności i spójności bazy danych jako całości.
Dobrze zorganizowana baza danych ma kluczowe znaczenie dla wielu funkcji, ale bez ustalonych protokołów przechowywania danych i manipulacji nimi może dojść do chaosu.
Ograniczenia SQL służą do ustalania wytycznych dotyczących przechowywania danych w określonej tabeli. Ograniczenia te umożliwiają systemowi wygenerowanie komunikatu o błędzie podczas próby wstawienia lub aktualizacji informacji, które są sprzeczne z ustalonymi parametrami. Egzekwując takie reguły, bazy danych mogą zachować spójność danych i wymusić przestrzeganie standardów we wszystkich tabelach.
Istnieje kilka odmian ograniczeń SQL, które są bardzo korzystne w zapewnianiu integralności danych i bezpieczeństwa w bazie danych.
Ograniczenie NOT NULL
Domyślnie kolumny bazy danych pozwalają na przechowywanie wartości null. Wartość null oznacza brak jakiejkolwiek wartości. I odwrotnie, ograniczenie not-null nakazuje, aby dana kolumna wykluczała wartości null i zamiast tego wymagała wartości merytorycznej.
Deklaratywna deklaracja służy do egzekwowania wymogu, że każdy wiersz w tabeli musi mieć odpowiedni wpis w odpowiednich kolumnach niebędących wartościami null, które są oznaczone ograniczeniem “NOT NULL”. Innymi słowy, nie jest dozwolone wstawianie lub w inny sposób wprowadzanie nowego rekordu do bazy danych, chyba że wszystkie wymagane pola są wypełnione danymi, ponieważ w przeciwnym razie zostanie wygenerowany błąd.
Rozważmy przypadek, w którym tabelaryczna reprezentacja klientów jest uważana za niezbędną do celów organizacyjnych. W takim scenariuszu rozsądne może być uwzględnienie pewnych niezbędnych szczegółów dotyczących każdego indywidualnego patrona we wspomnianej bazie danych, a jednym z takich podstawowych szczegółów jest nomenklatura patrona. Aby zagwarantować zgodność z wymaganiami dotyczącymi wprowadzania danych, do tych obowiązkowo uwzględnianych atrybutów można zastosować ograniczenie znane jako “NOT NULL”, zapewniając w ten sposób, że żadne konto klienta nie będzie istnieć bez podania tych kluczowych informacji identyfikujących.
CREATE TABLE Customers (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Mobile_No int NOT NULL,
Age int
);
W przypadku próby wstawienia rekordu klienta pozbawionego pola Wiek, baza danych potwierdzi taką próbę bez generowania jakichkolwiek komunikatów o błędach:
INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 723000000);
Chociaż można próbować wstawić rekord bez uwzględnienia pola “FirstName”, system bazy danych wygeneruje w odpowiedzi komunikat o błędzie:
ERROR: null value in column "firstname" violates not-null constraint
Detail: Failing row contains (123456, Dior, null, 723000000, null).
PRIMARY KEY Constraint
Klucz podstawowy jest istotnym elementem każdego systemu zarządzania relacyjną bazą danych, służącym jako odrębny identyfikator dla każdego wpisu w określonej tabeli. To oznaczenie gwarantuje wyjątkowość i indywidualność danych zawartych we wspomnianej kolumnie (kolumnach), wykluczając duplikację przy jednoczesnym zachowaniu organizacji i integralności wszystkich przechowywanych informacji.
W każdej relacyjnej bazie danych klucz podstawowy jest niezbędnym elementem, który składa się z charakterystycznych wartości i nie może zawierać zer. Fundamentalna zasada strukturyzacji tabel mówi, że każda tabela powinna posiadać tylko jeden klucz główny. Ten klucz główny może składać się z pojedynczej kolumny lub kilku kolumn, zwanych łącznie złożonymi kluczami głównymi.
Podczas konstruowania bazy danych informacji o klientach konieczne jest ustanowienie unikalnych kodów identyfikacyjnych dla każdego klienta. Aby osiągnąć ten cel, można wdrożyć koncepcję ograniczenia klucza podstawowego, która zapewnia, że żaden z dwóch klientów nie posiada identycznych numerów identyfikacyjnych.
Aby zaimplementować ograniczenie klucza głównego w bazie danych MySQL, można odwołać się do następującego przykładowego kodu:
CREATE TABLE Customers (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
Baza danych odrzuci każdą próbę przesłania przez użytkownika danych zawierających zduplikowany identyfikator. W przypadku wystąpienia takiego zdarzenia zostanie zgłoszony wyjątek oznaczający istnienie zduplikowanych informacji. W przedstawionym scenariuszu osoba próbuje dodać dwa wpisy o identycznym numerze identyfikacyjnym:
INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'John', 'Doe', 35 );
INSERT INTO Customers (ID, LastName, FirstName, Age)
VALUES (1, 'Mary', 'Jane', 35 );
Baza danych wyświetli komunikat o błędzie:
Duplicate entry '1' for key 'PRIMARY'
Implementacja klucza głównego służy zapobieganiu występowania zduplikowanych wartości identyfikatora w ramach danego zbioru danych, o czym świadczy fakt, że zmiana unikalnego identyfikatora przypisanego do konkretnego rekordu spowoduje odrzucenie próby operacji wstawienia lub aktualizacji przez system bazy danych. Funkcja ta jest szczególnie ważna dla zachowania integralności danych i uniknięcia niespójności w przypadku dużych zbiorów informacji, takich jak te znajdujące się w rekordach klientów.
FOREIGN KEY Constraint
Klucze obce służą do tworzenia powiązań między dwiema tabelami poprzez odwołanie się do klucza podstawowego jednej tabeli w tabeli drugorzędnej lub powiązanej. Pozwala to na połączenie i współzależność między danymi zawartymi w tych oddzielnych jednostkach, ułatwiając tworzenie linków i połączeń poprzez wspólne podobieństwa.
Związek między dwiema tabelami w relacyjnej bazie danych można ustanowić za pomocą kluczy podstawowych i obcych. W tym kontekście tabela, która służy jako podstawowe źródło danych i posiada unikalny identyfikator dla każdego rekordu, jest określana jako tabela “nadrzędna”, podczas gdy tabela, która opiera się na informacjach z innej tabeli w celu ustanowienia własnych rekordów, jest znana jako tabela “podrzędna”. Wynika z tego, że każdy wpis zarejestrowany w tabeli podrzędnej musi odpowiadać istniejącemu rekordowi w tabeli nadrzędnej, służąc jako środek powiązania i walidacji.
Wdrożenie ograniczenia klucza obcego zapewnia zachowanie relacji między połączonymi tabelami bazy danych, zabraniając wszelkich operacji, które mogą zagrozić tym połączeniom. W szczególności usunięcie lub usunięcie tabeli, która służy jako klucz obcy do innej tabeli, jest ograniczone, co wymaga jednoczesnego zakończenia obu powiązanych tabel w celu uniknięcia zakłócenia ich powiązania.
W przeciwieństwie do klucza głównego, który jest unikalny w ramach tabeli, klucze obce mogą istnieć w wielu instancjach w ramach jednej tabeli i mogą zawierać wartości null. Na przykład w przedstawionej ilustracji utworzenie zamówienia wymaga użycia wartości klucza obcego customer\_id.
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
INSERT INTO Customers(customer_id, first_name, last_name)
VALUES (1, 'Christian', 'Dior');
INSERT INTO Orders(order_id, customer_id, order_date)
VALUES (1, 1, '2023-08-07');
Podczas próby wygenerowania transakcji bez wcześniej istniejącego identyfikatora klienta, system wyświetli powiadomienie o błędzie.
Cannot add or update a child row: a foreign key constraint fails
(`db_9_4ee205c`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`customer_id`) REFERENCES `customers` (`customer_id`))
Ograniczenie UNIQUE
Unikalne ograniczenie to mechanizm w relacyjnych bazach danych, który gwarantuje unikalność wartości określonej kolumny we wszystkich wierszach tabeli. Podobnie jak w przypadku kluczy głównych, ograniczenie to utrzymuje spójność danych, zapobiegając powielaniu informacji. Niewdrożenie takiego ograniczenia może skutkować niezorganizowaną bazą danych wymagającą ręcznej interwencji w celu zidentyfikowania i usunięcia zbędnych rekordów.
Można wykorzystać wiele ograniczeń UNIQUE w ramach jednej tabeli w przeciwieństwie do pojedynczego klucza PRIMARY KEY, jak pokazano na przykładzie tworzenia tabeli “Customers” z charakterystycznymi identyfikatorami i numerami telefonów. Wdrożenie takiego ograniczenia odbywa się poprzez zastosowanie następującej składni w systemie zarządzania bazą danych MySQL:
CREATE TABLE Customers (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Mobile_No BIGINT UNIQUE
);
W przypadkach, gdy w bazie danych dokonano wielu wpisów wykorzystujących identyczne numery telefonów, zostanie wygenerowane powiadomienie o błędzie wskazujące na taką duplikację.
INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (123456, 'Dior', 'Christian', 254000000 );
INSERT INTO Customers (ID, LastName, FirstName, Mobile_No)
VALUES (7891011, 'Dedan', 'Kimathi', 254000000 );
Powiadomienie lub alert generowany w odpowiedzi na nieudaną próbę logowania, który zawiera informacje o koncie użytkownika i wszelkich powiązanych uprawnieniach, zazwyczaj ma standardowy format składający się z określonych szczegółów, takich jak data i godzina nieudanej próby uwierzytelnienia, nazwa użytkownika lub nazwa konta powiązana z próbą, krótki opis charakteru niepowodzenia (np, nieprawidłowe hasło) oraz odpowiednie instrukcje dotyczące rozwiązania problemu
Duplicate entry '254000000' for key 'Mobile_No'
Ograniczenie UNIQUE, które jest specyficznym typem ograniczenia bazy danych, gwarantuje, że żadne dwa rekordy w bazie danych nie będą miały identycznych wartości dla pól identyfikatora klienta lub numeru telefonu komórkowego.
Ograniczenie CHECK
Ograniczenie CHECK służy do regulowania dopuszczalnego zakresu wartości, które można wprowadzić w określonej kolumnie, utrzymując w ten sposób ogólną integralność i autentyczność przechowywanych w niej danych. Nakładając takie ograniczenia na określoną kolumnę, można ograniczyć ją do akceptowania tylko tych wartości, które zostały wyraźnie usankcjonowane, zapewniając w ten sposób dodatkową warstwę zabezpieczeń przed potencjalnymi rozbieżnościami lub nieautoryzowanymi wpisami.
Ograniczenie CHECK wymaga, aby każdy wyznaczony wiersz lub rekord w określonej tabeli został oceniony jako prawdziwy lub nieznany. W przypadku fałszywej oceny, baza danych wyświetli powiadomienie o błędzie.
W celu ograniczenia dostępu do niektórych rekordów klientów na podstawie wieku, możliwe jest wykorzystanie ograniczenia sprawdzającego w tabeli Klienci danego systemu zarządzania relacyjną bazą danych, takiego jak PostgreSQL. Ograniczenie to wymagałoby, aby wszystkie osoby ubiegające się o usługę miały co najmniej osiemnaście lat, zapobiegając w ten sposób wszelkim próbom świadczenia usług nieletnim. Poniżej znajduje się ilustracja tej koncepcji w postaci kodu SQL:
CREATE TABLE Customers (
ID int NOT NULL,
Age int CHECK(Age>=18),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Mobile_No BIGINT UNIQUE
);
Jeśli ktoś spróbuje wprowadzić wiek klienta, który jest poniżej progu 18 lat,
INSERT INTO Customers (ID, Age, LastName, FirstName, Mobile_No)
VALUES (123456, 15, 'Dior', 'Christian', 1254000000 );
Baza danych może wyświetlić powiadomienie wskazujące, że wystąpił problem, który może przypominać następujący format:
ERROR: new row for relation "customers" violates check constraint
"customers_age_check"
Detail: Failing row contains (123456, 15, Dior, Christian, 1254000000)
Jak dodawać i usuwać ograniczenia SQL z baz danych
Elastyczność zapewniana przez ograniczenia SQL jest cennym atutem, ponieważ można je modyfikować za pomocą instrukcji ALTER TABLE. To wszechstronne polecenie umożliwia użytkownikom wprowadzanie zmian w ograniczeniach tabeli w celu dostosowania do zmieniających się wymagań dotyczących danych.
Oprócz wspomnianych tutaj ograniczeń SQL, istnieje szeroki zakres ograniczeń, które można zbadać w celu stworzenia niestandardowej bazy danych. Rozpoczęcie od tych opisanych w tym artykule byłoby odpowiednim punktem wyjścia.