Definicja ograniczeń integralności. Tworzenie kluczy podstawowych Ułamkowe typy danych liczbowych

Sekcje witryny

Wybór redaktorów:

Reklama

dom - Praca

KLUCZ PODSTAWOWY – ograniczenie

KLUCZ PODSTAWOWY to ograniczenie integralności na poziomie kolumny — zbiór obsługiwanych reguł — które formalnie oznacza kolumnę lub grupę kolumn jako unikalny identyfikator każdego wiersza w tabeli.

Jeśli do Firebird przyszedłeś z systemów baz danych, które obsługują koncepcję „indeksu podstawowego” do ustalania klucza (zwykle systemy plikowe, takie jak Paradox, Access i MySQL), to Firebird i świat standardów SQL są Ci znane. Klucz podstawowy nie jest indeksem, ale ograniczeniem. Jedną z zasad takiego ograniczenia jest to, że ograniczenie musi mieć określony, unikalny indeks jednego lub większej liczby powiązanych z nim niepustych elementów.

Samo utworzenie takiego indeksu nie powoduje utworzenia klucza podstawowego. Jednakże utworzenie ograniczenia klucza podstawowego skutkuje utworzeniem wymaganego indeksu składającego się z kolumn wymienionych w deklaracji ograniczenia.

UWAGA! Nie ma potrzeby importowania istniejącego „indeksu podstawowego” ze starszego systemu opartego na plikach ani tworzenia takiego indeksu w oczekiwaniu na zadeklarowanie ograniczenia klucza podstawowego. Firebird nie może nałożyć ograniczenia klucza podstawowego na istniejący indeks - przynajmniej w istniejących wersjach, w tym 1.5 - a optymalizator zapytań nie będzie działał poprawnie, gdy indeksy zostaną zduplikowane.

Tabela może mieć tylko jeden klucz podstawowy. Kiedy definiujesz ograniczenie, Firebird automatycznie tworzy wymagany indeks, korzystając z różnych nazwanych reguł. Nazwy indeksów klucza podstawowego zostaną omówione dalej.

UWAGA! Jeśli konwertujesz bazę danych do Firebirda z dowolnego źródła innego niż InterBase lub Oracle, musisz zwrócić szczególną uwagę na schemat dotyczący nazw kluczy podstawowych i ograniczeń.

Chociaż samo ograniczenie PRIMARY KEY nie jest ograniczeniem referencyjnym, zwykle jest wymaganą częścią dowolnego ograniczenia referencyjnego, będąc potencjalnym przedmiotem klauzuli REFERENCES ograniczenia FOREIGN KEY. Szczegóły znajdziesz w Rozdziale 17.

Wybór klucza głównego

Identyfikacja kolumn jako kandydatów na klucz podstawowy wykracza poza zakres tej publikacji. Napisano wiele znakomitych książek na temat normalizacji, procesu zmniejszania redundancji i powtarzania grup w zbiorach danych oraz prawidłowego identyfikowania elementu, który jednoznacznie reprezentuje pojedynczy wiersz w tabeli. Jeśli dopiero zaczynasz przygodę z relacyjnymi bazami danych, koszt zapoznania się z dobrą książką na temat modelowania danych nie będzie zbyt duży.

Kandydat na klucz podstawowy, którym może być pojedyncza kolumna lub grupa kolumn, ma dwa wymagane wymagania.

* Atrybut NOT NULL musi być zadeklarowany dla wszystkich kolumn w grupie składającej się z jednej lub większej liczby kolumn. Integralność klucza można osiągnąć jedynie poprzez porównanie wartości, a wartość NULL nie jest wartością.

*Kolumna lub grupa kolumn musi być unikalna - tzn. w tabeli nie może pojawić się więcej niż jeden wiersz z tymi samymi wartościami. Na przykład prawo jazdy lub numer ubezpieczenia społecznego mogą zostać uznane za kandydatów, ponieważ są generowane przez systemy, które nie pozwalają na duplikowanie numerów.

Do tych teoretycznych „postaw” należy dodać trzecią.

* Całkowity rozmiar kluczy kandydujących musi wynosić 252 bajty lub mniej. To nie tylko kwestia liczenia znaków. Limit ten należy zmniejszyć – w niektórych przypadkach radykalnie – jeśli istnieje wiele kolumn, niebinarna sekwencja zestawiania lub zestaw znaków wielobajtowych.

Jak prawdziwe dane mogą skazać Cię na porażkę

Korzystając z tabeli bazy danych Employee.fdb EMPLOYEE z katalogu /examples w katalogu głównym Firebirda (employee.gdb w wersji 1.0.x), przyjrzyjmy się, jak dane ze świata rzeczywistego mogą sprawić, że Twoje teoretyczne założenia dotyczące unikalności będą błędne. Oto deklaracja pokazująca istotne dane przechowywane w tej tabeli:

UTWÓRZ TABELI PRACOWNIKÓW (

FIRST_NAME VARCHAR(15) NIE NULL,

/* założenie: pracownik musi mieć imię */

LAST_NAME VARCHAR(20) NIE NULL,

/* założenie: pracownik musi mieć nazwisko */

PHONE_EXT VARCHAR(4),

HIRE_DATE DATA DOMYŚLNA CURRENT_DATE NIE NULL,

DEPT_NO CHAR(3) NIE NULL,

JOB_CODE VARCHAR (5) NIE NULL,

JOB_GRADE SMALLINT NIE NULL,

JOB_COUNTRY VARCHAR(15) NIE NULL,

WYNAGRODZENIE NUMERYCZNE (15, 2) DOMYŚLNE 0 NIE NULL,

IMIĘ IMIĘ WYLICZONE PRZEZ IMIĘ || || NAZWISKO) ;

W rzeczywistości ta struktura nie ma kluczy kandydujących. Nie jest możliwe zidentyfikowanie pojedynczego wiersza pracownika przy użyciu (FIRST_NAME, LAST_NAME) jako klucza, ponieważ prawdopodobieństwo powielenia kombinacji tych dwóch elementów w organizacji jest średnie lub duże. Nie będziemy mogli zapisać danych dwóch pracowników o nazwisku John Smith.

Aby zdobyć klucze, musisz coś wymyślić. To „coś” jest mechanizmem znanym jako klucz zastępczy.

Klucze zastępcze

Klucz zastępczy omówiliśmy już we wstępie dotyczącym kluczy w rozdziale 14. Zastępczy klucz podstawowy to wartość, która jest gwarantowana jako unikalna i nie ma treści semantycznej i jest używany jako klucz zastępczy w strukturze tabeli, która nie może zapewnić kandydata klucz do własnej struktury. Z tego powodu do tabeli EMPLOYEE (zadeklarowanej w domenie) dodawany jest EMP_NO, który pełni rolę klucza zastępczego:

UTWÓRZ DOMENĘ EMPNO SMALLINT;

ZMIEŃ PRACOWNIKA TABELI

DODAJ EMP_NO EMPNO NIE NULL,

DODAJ OGRANICZENIE PK_EMPLOYEE

KLUCZ PODSTAWOWY(EMP_NO) ;

Ta baza danych zawiera także generator o nazwie EMP_NO_GEN i wyzwalacz przed wstawieniem o nazwie SET_EMP_NO w tabeli EMPLOYEE, który pozwala na pobranie wartości danego klucza po dodaniu nowego wiersza. W rozdz. „Implementowanie kluczy automatycznego zwiększania” w rozdziale 31 szczegółowo opisuje tę technikę. Jest to zalecany sposób implementacji kluczy zastępczych w Firebird.

Możesz rozważyć korzyści wynikające ze stosowania zastępczego klucza podstawowego nie tylko w przypadku, gdy tabela nie może zaoferować kandydata, ale także w przypadkach, gdy kluczowy kandydat jest złożony.

Złożone klucze podstawowe

Analizując dane, czasami można znaleźć pojedynczą unikalną kolumnę w strukturze danych. Teoria polega na znalezieniu dwóch lub więcej kolumn zgrupowanych razem jako klucz, który zagwarantuje niepowtarzalność wiersza. Kiedy wiele kolumn łączy się w klucz, nazywa się go kluczem złożonym lub czasami kluczem złożonym.

Jeśli masz doświadczenie z systemami baz danych, takimi jak Paradox, które wykorzystywały klucze złożone do implementacji relacji hierarchicznych, może ci być trudno wyobrazić sobie życie bez nich. Jak dotąd w praktyce klucze złożone należy uważać za bardzo ograniczone w systemach DBMS takich jak Firebird, które nie przechodzą przez fizyczne struktury indeksów na dysku w celu implementacji relacji.

W Firebird nie ma potrzeby stosowania indeksów złożonych, a ponadto indeksy złożone stwarzają pewne problemy związane z projektowaniem i wydajnością w przypadku dużych tabel.

* Klucze złożone zazwyczaj składają się z nieatomowych elementów kluczowych, tj. wybrane kolumny mają znaczenie (są „znaczącymi danymi”) i z pewnością są podatne na zmiany zewnętrzne i błędy ręcznego wprowadzania danych.

* Klucze obce z innych tabel odwołujących się do tej tabeli spowodują zduplikowanie każdego elementu klucza złożonego. Integralność referencyjna jest zagrożona w przypadku używania kluczy nieatomowych. Połączenie pierwiastków nieatomowych zwiększa ryzyko.

* Klucze obce, podobnie jak klucze podstawowe, mają stałe indeksy. Indeksy złożone mają bardziej rygorystyczne ograniczenia dotyczące rozmiaru niż indeksy jednokolumnowe.

* Indeksy złożone są zwykle duże. Duże indeksy wykorzystują więcej stron bazy danych, co powoduje, że operacje na indeksach (sortowanie, łączenie i porównywanie) są wolniejsze niż to konieczne.

Atomowość kolumn klucza podstawowego

Dobrą praktyką jest nieuwzględnianie w kluczach podstawowych i obcych kolumn, które mają znaczenie jako dane. Narusza to jedną z podstawowych zasad projektowania relacyjnych baz danych – atomowość. Zasada atomowości wymaga, aby każdy element danych istniał całkowicie samodzielnie i podlegał jednej wewnętrznej zasadzie rządzącej jego istnieniem.

Aby klucz podstawowy był atomowy, musisz wykraczać poza ludzkie decyzje. Jeśli ludzie to komponują lub klasyfikują, nie jest to atomowe. Jeśli podlega jakiejkolwiek regule innej niż NOT NULL i wymaganiom dotyczącym niepowtarzalności, nie jest niepodzielny. W powyższym przykładzie nawet prawo jazdy czy numer PESEL nie spełnia wymagań atomowości dla klucza podstawowego, gdyż są one podmiotem systemów zewnętrznych.

Składnia deklaracji klucza podstawowego

Aby przypisać ograniczenie PRIMARY KEY do kolumny lub grupy kolumn, można użyć kilku opcji składni. Wszystkie kolumny będące elementami klucza podstawowego muszą być predefiniowane z atrybutem NOT NULL. Ponieważ nie można dodać ograniczenia NOT NULL do kolumny po jej utworzeniu, należy zadbać o to ograniczenie przed użyciem innych ograniczeń.

Ograniczenie KLUCZ PODSTAWOWY można zastosować w dowolnej z następujących faz tworzenia metadanych:

* w definicji kolumny w instrukcji CREATE TABLE lub ALTER TABLE jako część definicji kolumny;

* w definicji tabeli w instrukcji CREATE TABLE lub ALTER TABLE jako oddzielnie zdefiniowane ograniczenie tabeli.

Definiowanie klucza podstawowego jako części definicji kolumny

Następująca sekwencja tworzy i zatwierdza domenę nie dopuszczającą wartości null, następnie definiuje kolumnę klucza podstawowego na podstawie tej domeny i jednocześnie stosuje do tej kolumny ograniczenie PRIMARY KEY:

UTWÓRZ DOMENĘ D_IDENTITY JAKO BIGINT NIE NULL;

UTWÓRZ OSObę STOŁOWĄ (

KLUCZ PODSTAWOWY PERSON_ID D_IDENTITY,

Firebird tworzy ograniczenie tabeli o nazwie INTEG_M i indeks o nazwie RDB$PRIMARYnn. (w każdym przypadku pl to liczba uzyskana z generatora. Obie liczby nie są ze sobą powiązane.) Nie masz wpływu na to, jakie są te nazwy, ani nie możesz ich zmienić.

Wynik będzie podobny, jeśli zastosujesz to samo podejście przy dodawaniu kolumny, użyciu instrukcji ALTER TABLE i utworzeniu klucza podstawowego w jednej instrukcji:

ZMIEŃ KSIĄŻKĘ STOŁOWĄ

DODAJ KLUCZ PODSTAWOWY BOOK_ID D_IDENTITY;

Definiowanie klucza podstawowego jako nazwanego ograniczenia

Innym sposobem zdefiniowania klucza podstawowego w definicji tabeli jest dodanie deklaracji ograniczenia na końcu definicji kolumn. Deklaracje ograniczeń umieszczane są na końcu, ponieważ zależą od istnienia kolumn, do których się odnoszą. Ta metoda umożliwia nazywanie ograniczeń. Następująca deklaracja nazywa ograniczenie klucza podstawowego jako PK_ATABLE:

UTWÓRZ TABELĘ ATABLE (

Identyfikator BIGINT NIE NULL,

ANOTHER_COLUMN VARCHAR(20),

CONSTRAINT PK_ATABLE KLUCZ PODSTAWOWY(ID));

Teraz zamiast używać wygenerowanej przez system nazwy RDB$PRIMARYnnn, Firebird używa PK_ATABLE jako nazwy tego ograniczenia. W Firebird 1.5 i nowszych wersjach stosuje również zdefiniowaną przez użytkownika nazwę ograniczenia do wspierającego unikalnego indeksu. W tym przykładzie indeks będzie miał nazwę PK_ATABLE, podczas gdy w innych wersjach będzie miał nazwę RDB$PRIMARYnnn.

Firebird 1.5 umożliwia także użycie nazw zdefiniowanych przez użytkownika dla ograniczenia i indeksu, który je obsługuje.

Korzystanie z niestandardowego indeksu

Przed wersją Firebird 1.5 nie było możliwości użycia indeksu malejącego do obsługi klucza podstawowego. Od wersji 1.5 możliwe jest utrzymanie klucza podstawowego z indeksem malejącym. Aby to zrobić, Firebird 1.5 dodaje rozszerzenie składni w postaci klauzuli USING, która umożliwia utworzenie indeksu ASC (rosnąco) lub DESC (malejąco) i nadanie mu nazwy innej niż nazwa ograniczenia.

AS c i DESC określają kierunek poszukiwań. Koncepcję tę omówiono bardziej szczegółowo w rozdziale 18.

Poniższa instrukcja utworzy ograniczenie klucza podstawowego o nazwie PK ATEST i pomocniczy indeks malejący o nazwie IDX_PK_ATEST:

UTWÓRZ Atest tabeli (

Identyfikator BIGINT NIE NULL,

DANE VARCHAR(10));

ZMIEŃ Atest tabeli

DODAJ OGRANICZENIE PK_ATEST KLUCZ PODSTAWOWY (ID)

KORZYSTANIE Z INDEKSÓW DESC IDX_PK_ATEST;

Alternatywna składnia będzie również działać:

UTWÓRZ Atest tabeli (

Identyfikator BIGINT NIE NULL,

DANE VARCHAR(10),

Ograniczenie PK_ATEST KLUCZ PODSTAWOWY (ID)

KORZYSTANIE Z OPISU INDEKS IDX Atest PK;

UWAGA! Jeśli utworzysz indeks DESCENDING na podstawie klucza podstawowego lub unikalnego, musisz określić USING DESC INDEX dla wszystkich kluczy obcych, które się do niego odwołują.

Dodaj klucz podstawowy do istniejącej tabeli

Dodanie do tabeli ograniczeń może zostać opóźnione. Powszechną praktyką wśród programistów jest definiowanie wszystkich tabel bez ograniczeń tabel, a następnie dodawanie ich za pomocą osobnego skryptu. Główny powód tej praktyki: duże skrypty często zawodzą, ponieważ autorzy zapominają o pewnych zależnościach. Po prostu mniej będzie Cię to bolało, jeśli utworzysz bazę danych w kolejności, która skróci czas i frustrację związaną z naprawianiem błędów zależności i ponownym uruchamianiem skryptów.

Zwykle w pierwszym skrypcie deklarujemy tabele i potwierdzamy ich utworzenie:

UTWÓRZ TABELĘ ATABLE (

Identyfikator BIGINT NIE NULL,

ANOTHER_COLUMN VARCHAR(20),

< другие столбцы >) ;

UTWÓRZ TABELI INNEGO TABELI (

ZMIEŃ TABELĘ ATABLE

DODAJ OGRANICZENIE PK_ATABLE

KLUCZ PODSTAWOWY (ID);

ZMIEŃ TABELI INNEGO TABELI...

W następnym rozdziale, gdy przyjrzymy się definicjom KLUCZ OBCY, oczywiste staną się korzyści płynące z tworzenia bazy danych w sekwencji wiarygodnych zależności.

Z książki Bazy danych: notatki z wykładów autor Autor nieznany

3. Ograniczenie integralności przez stan Ograniczenie integralności relacyjnego obiektu danych przez stan to tzw. Niezmiennik danych. W tym przypadku integralność należy zdecydowanie odróżnić od bezpieczeństwa, co z kolei oznacza ochronę danych

Z książki Język programowania C# 2005 i platforma .NET 2.0. przez Troelsena Andrew

Ograniczanie użycia atrybutów Domyślnie niestandardowe atrybuty można zastosować do dowolnej części kodu programu (metod, klas, właściwości itp.). Dlatego jeśli tylko ma to sens, można użyć VehicleDescription do zdefiniowania (m.in.) metod,

Z książki DIY Serwer Linux autor

11.2.2. Ograniczanie dostępu Myślę, że konieczne jest przyjrzenie się bliżej dyrektywie Limit block. Dyrektywa ta określa rodzaj i parametry dostępu do konkretnego katalogu. Rozważ Listing 11.9. Listing 11.9. Przykład użycia dyrektywy Limit

Z książki Podstawy programowania obiektowego przez Meyera Bertranda

Z książki Programowanie w Prologu dla sztucznej inteligencji autor Bratko Iwan

Ograniczanie dostępu klienta Aby ograniczyć dostęp klienta do określonego komponentu h, zostanie wykorzystana możliwość umieszczenia dwóch lub więcej sekcji funkcji w deklaracji klasy. Deklaracja wyglądałaby następująco: klasa S2 cechaf ...g ...cecha (A, B)h ......endComponents f i g

Z książki Podwojenie sprzedaży w sklepie internetowym autor Parabellum Andriej Aleksiejewicz

5.1. Ograniczanie wyszukiwania W procesie osiągania celu system Prolog automatycznie przeszukuje opcje, powracając w przypadku niepowodzenia którejś z nich. Ten typ wyszukiwania jest użytecznym mechanizmem oprogramowania, ponieważ uwalnia użytkownika od konieczności

Z książki PRZEWODNIK DEWELOPERA BAZY DANYCH Firebird przez Borriego Helena

Ograniczenia Drugim elementem formuły EDI jest termin (z angielskiego terminu), czyli ograniczenie. Może to być limit czasowy (na przykład 50% rabatu tylko przez dwa dni) lub limit ilościowy (na przykład cenny prezent dla pierwszych 50 klientów). Co więcej, krótkie terminy działają

Z książki Omert@. Przewodnik po bezpieczeństwie komputerów i ochronie informacji dla wielkich szefów przez Axlera Alexa

Ograniczenie referencyjne Ograniczenie referencyjne jest implementowane jako KLUCZ OBCY. Ograniczenie klucza obcego istnieje tylko w kontekście innej tabeli i unikalnego klucza tej tabeli, określonego jawnie lub pośrednio w klauzuli REFERENCES, gdy deklarowane jest ograniczenie Tables

Z książki Linux oczami hakera autor Flenow Michaił Jewgienijewicz

NOT NULL Ograniczenie Firebird nie obsługuje atrybutu wartości null, jak to robią niektóre niestandardowe systemy DBMS. Zgodnie ze standardami wszystkie kolumny w Firebird mogą zawierać wartość null, chyba że określono wyraźne ograniczenie NOT NULL.

Z książki InterBase World. Architektura, administracja i rozwój aplikacji bazodanowych w InterBase/FireBird/Yaffil autor Kowiazyn Aleksiej Nikołajewicz

Z książki Anonimowość i bezpieczeństwo w Internecie. Od „czajniczka” do użytkownika autor Kolisnichenko Denis Nikołajewicz

Z książki autora

4.11.5. Ograniczenia sieci W dużych sieciach bardzo trudno jest opisać każdy komputer. Aby ułatwić to zadanie, możesz skorzystać z wpisów grupowych. Przykładowo musisz zezwolić na dostęp do Internetu tylko sieci 192.168.1.x (z maską 255.255.255.0). Oznacza to, że pierwsze 24 bity (pierwsze trzy

Z książki autora

9.5.8. Ograniczenie kanałów Organizując dostęp do Internetu, indywidualni użytkownicy często muszą zapewnić wyższe prędkości połączenia. Jak to zrobić, skoro domyślnie wszyscy są równi i mogą pracować na maksymalnej dostępnej w danej chwili prędkości?

Z książki autora

Ograniczenie CHECK Jednym z najbardziej przydatnych ograniczeń w bazie danych jest ograniczenie sprawdzające. Pomysł jest bardzo prosty - sprawdź wartość wstawioną do tabeli pod kątem jakiegoś warunku i w zależności od tego, czy warunek jest spełniony, wstaw dane lub nie

Z książki autora

P2.4. Ograniczenie dostępu P2.4.1. Odmowa dostępu do witryny (lub listy witryn) Załóżmy, że musisz zablokować dostęp do określonej witryny (lub listy witryn). W tym celu przejdź do zakładki Rozliczenia | Klienci | Filtry | Przed grupą (ryc. A2.10). Wykonaj polecenie menu Akcja |

Z książki autora

P2.4.2. Ograniczenie prędkości Aby ograniczyć prędkość konkretnego użytkownika, zaznacz go na liście użytkowników, kliknij prawym przyciskiem myszy i wybierz Właściwości. Przejdź do zakładki Ograniczenia (rys. A2.14), odznacz pole Domyślne w oknie

Klucz podstawowy to pole w tabeli, które jednoznacznie identyfikuje każdy wiersz/rekord w tabeli bazy danych. Klucze podstawowe muszą zawierać unikalne wartości. Kolumna klucza podstawowego nie może mieć wartości.

Tabela może mieć tylko jeden klucz podstawowy, który może składać się z jednego lub większej liczby pól. Kiedy jako klucz podstawowy używanych jest wiele pól, nazywa się je kluczem złożonym.

Jeśli tabela ma klucz podstawowy zdefiniowany w dowolnym polu (polach), nie można mieć dwóch rekordów mających tę samą wartość w tych polach.

Notatka– Można wykorzystać te pojęcia podczas tworzenia tabel bazy danych.

Tworzenie klucza podstawowego

Oto składnia definiująca atrybut ID jako klucz podstawowy w tabeli Klienci.

UTWÓRZ TABELI KLIENTÓW (ID INT NIE NULL, NAZWA VARCHAR (20) NIE NULL, WIEK INT NIE NULL, ADRES CHAR (25) , WYNAGRODZENIE DZIESIĘTNE (18, 2), KLUCZ PODSTAWOWY (ID));

Aby utworzyć ograniczenie klucza podstawowego w kolumnie „ID”, gdy tabela KLIENCI już istnieje, użyj następującej składni SQL:

ZMIEŃ KLIENTÓW TABELI DODAJ KLUCZ PODSTAWOWY (ID);

Notatka

Jeśli do dodania klucza podstawowego użyjesz instrukcji ALTER TABLE, kolumna(y) klucza podstawowego musi zostać już zadeklarowana jako niezawierająca wartości NULL (jeśli tabela została utworzona jako pierwsza).

Aby zdefiniować klucz podstawowy w wielu kolumnach, użyj poniższej składni SQL:

UTWÓRZ TABELI KLIENTÓW (ID INT NIE NULL, NAZWA VARCHAR (20) NIE NULL, WIEK INT NIE NULL, ADRES CHAR (25) , WYNAGRODZENIE DZIESIĘTNE (18, 2), KLUCZ PODSTAWOWY (ID, NAZWA));

Aby utworzyć ograniczenie klucza podstawowego dla kolumn ID i NAME, gdy tabela CUSTOMERS już istnieje, użyj następującej składni SQL.

ZMIEŃ KLIENTÓW TABELI DODAJ OGRANICZENIE PK_CUSTID KLUCZ PODSTAWOWY (ID, NAZWA);

Usuwanie klucza podstawowego

Możesz usunąć ograniczenia klucza podstawowego z tabeli, korzystając ze składni podanej poniżej.

ZMIEŃ TABELI KLIENCI UPUSZCZAJĄ KLUCZ PODSTAWOWY;

Zwracam uwagę na bezpłatne tłumaczenie artykułu SQL dla początkujących, część 2

Dla każdego twórcy stron internetowych ważna jest możliwość interakcji z bazami danych. W drugiej części kontynuujemy naukę języka SQL-a i zastosować nasze umiejętności w DBMS MySQL-a. Przyjrzymy się indeksom, typom danych i bardziej złożonym zapytaniom.

Czego potrzebujesz

Proszę zapoznać się z sekcją „Czego potrzebujesz” w pierwszej części, która znajduje się tutaj.

Jeśli chcesz uruchomić te przykłady na swoim serwerze, wykonaj następujące czynności:

Otwórz konsolę MySQL-a i zaloguj się.
Utwórz bazę danych „my_first_db” za pomocą zapytania TWORZYĆ, jeśli nie został wcześniej utworzony.
Zmień bazę za pomocą operatora UŻYWAĆ.

Indeksy

Indeksy (lub klucze) są powszechnie używane w celu poprawy szybkości wykonywania instrukcji wybierających dane (takich jak WYBIERAĆ) z tabel.

Są ważną częścią dobrej architektury baz danych; trudno je zaliczyć do „optymalizacji”. Zazwyczaj indeksy są dodawane na początku, ale można je dodać później za pomocą zapytania ZMIEŃ TABELĘ.

Główne powody indeksowania kolumn bazy danych to:

Prawie każda tabela ma klucz podstawowy ( GŁÓWNY KLUCZ), zwykle jest to kolumna „id”.
Jeśli kolumna ma przechowywać unikalne wartości, musi mieć unikalny indeks ( UNIKALNY).
Jeśli potrzebujesz częstego wyszukiwania w kolumnie (używając jej w zdaniu GDZIE), musi mieć regularny indeks ( INDEKS).
Jeśli kolumna jest powiązana z inną tabelą, powinien to być klucz obcy, jeśli to możliwe ( KLUCZ OBCY) lub zwykły indeks.

Klucz podstawowy (KLUCZ PODSTAWOWY)

Prawie wszystkie tabele mają klucz podstawowy, zwykle liczbę całkowitą z opcją automatycznego zwiększania ( AUTO_INCREMET).

Podzapytania często powodują znaczny spadek wydajności, dlatego używaj ich ostrożnie.

UNIA: Łączenie danych

Korzystanie z zapytania UNIA, możesz łączyć wyniki wielu zapytań SELECT.

Ten przykład łączy stany zaczynające się na literę „N” ze stanami o większej populacji:

(SELECT * FROM stany WHERE nazwa LIKE „n%”) UNION (SELECT * FROM stany WHERE populacja > 10000000);

Należy pamiętać, że Nowy Jork jest dużym stanem i zaczyna się na literę „N”. Jednak pojawia się tylko raz na liście, ponieważ duplikaty są usuwane automatycznie.

Także piękno próśb UNIA polega na tym, że można ich używać do łączenia zapytań względem różnych tabel.

Mamy na przykład tabele pracowników (pracowników), menedżerów (menedżerów) i klientów (klientów). Każda tabela posiada pole z adresem e-mail. Jeśli chcemy uzyskać wszystkie adresy e-mail w jednym żądaniu, możemy wykonać następujące czynności:

(WYBIERZ e-mail OD pracowników) UNION (WYBIERZ e-mail OD menedżerów) UNION (WYBIERZ e-mail OD klientów WHERE subskrybowany = 1);

Po spełnieniu tego żądania otrzymamy adresy pocztowe wszystkich pracowników i menadżerów, a jedynie tych klientów, którzy są zapisani na listę mailingową.

WSTAW Ciąg dalszy

O prośbie już rozmawialiśmy WSTAWIĆ w poprzednim artykule. Teraz, gdy przyjrzeliśmy się indeksom, możemy porozmawiać o dodatkowych możliwościach zapytań WSTAWIĆ.

WSTAW... W PRZYPADKU ZDUplikowanej aktualizacji klucza

Jest to najczęściej stosowany warunek. Najpierw żądanie próbuje zostać wykonane WSTAWIĆ, a jeśli żądanie nie powiedzie się z powodu zduplikowanego podstawowego ( GŁÓWNY KLUCZ) lub unikalny ( UNIKALNY KLUCZ), wówczas żądanie zostanie wykonane AKTUALIZACJA.

Najpierw utwórzmy tabelę testową.

To jest stół do przechowywania żywności. Pole „zapas” przechowuje liczbę produktów dostępnych w magazynie.

Spróbujmy teraz wstawić istniejącą wartość do tabeli i zobaczyć, co się stanie.

Otrzymaliśmy błąd.

Załóżmy, że mamy nową piekarnię i chcemy zaktualizować bazę danych, ale nie wiemy, czy w bazie danych jest już wpis. Możemy najpierw sprawdzić istnienie rekordu, a następnie uruchomić kolejne zapytanie wstawiające. Możesz też zrobić wszystko w jednym prostym zapytaniu:

Działa tak samo jak WSTAWIĆ, ale z jedną ważną cechą. Jeśli rekord już istnieje, zostanie usunięty, a następnie wykonane zostanie zapytanie WSTAWIĆ i nie będziemy otrzymywać żadnych komunikatów o błędach.

Uwaga, ponieważ Wstawiony zostaje zupełnie nowy wiersz, pole autoinkrementacji zwiększa się o jeden.

Jest to sposób, aby przede wszystkim zapobiec występowaniu błędów powielania, aby aplikacja mogła działać. Może zaistnieć potrzeba wstawienia nowej linii bez drukowania błędów, nawet jeśli nastąpi duplikacja.

Nie ma żadnych błędów ani zaktualizowanych linii.

Typy danych

Każda kolumna w tabeli musi być określonego typu. Użyliśmy już typów WEWN, VARCHAR I DATA, ale nie omawiał ich szczegółowo. Przyjrzymy się także kilku innym typom danych.

Zacznijmy od numerycznych typów danych. Dzielę je na dwie grupy: liczby całkowite i ułamki zwykłe.

Cały

W kolumnie całkowitej można przechowywać tylko liczby naturalne (bez przecinka dziesiętnego). Domyślnie mogą być dodatnie lub ujemne. Jeżeli opcja jest wybrana NIE PODPISANE, wówczas można przechowywać tylko liczby dodatnie.

MySQL obsługuje 5 typów liczb całkowitych o różnych rozmiarach i zakresach:

Ułamkowe typy danych liczbowych

Te typy mogą przechowywać liczby ułamkowe: FLOAT, DOUBLE i DECIMAL.

FLOAT zajmuje 4 bajty, DOUBLE zajmuje 8 bajtów i jest podobny do poprzedniego. PODWÓJNE jest dokładniejsze.

DECIMAL(M,N) ma zmienną precyzję. M to maksymalna liczba cyfr, N to liczba cyfr po przecinku.

Na przykład DECIMAL(13,4) ma 9 miejsc po przecinku i 4 miejsca po przecinku.

Typy danych ciągowych

Z nazwy można się domyślić, że mogą przechowywać ciągi znaków.

CHAR(N) może przechowywać N znaków i ma stałą wartość. Na przykład CHAR(50) musi zawsze zawierać 50 znaków w wierszu w całej kolumnie. Maksymalna możliwa wartość to 255 znaków

VARCHAR(N) działa tak samo, ale zakres może się różnić. N - oznacza wartość maksymalną. Jeśli przechowywany ciąg jest krótszy niż N znaków, zajmie mniej miejsca na dysku twardym. Maksymalna możliwa wartość to 65535 znaków.

Warianty typu TEXT są bardziej odpowiednie dla długich ciągów. TEXT ma limit 65535 znaków, MEDIUMTEXT ma limit 16,7 miliona, a LONGTEXT ma limit 4,3 miliarda znaków. MySQL zwykle przechowuje je w oddzielnych magazynach na serwerze, tak aby pamięć główna była jak mała i szybka.

Wniosek

Dziękujemy za przeczytanie artykułu. SQL to ważny język i narzędzie w arsenale twórców stron internetowych.

Ostatnia aktualizacja: 27.04.2019

Klucze obce pozwalają na nawiązanie relacji pomiędzy tabelami. Klucz obcy jest ustawiany na kolumny tabeli zależnej, podrzędnej i wskazuje na jedną z kolumn tabeli głównej. Zazwyczaj klucz obcy wskazuje na klucz podstawowy z powiązanej tabeli głównej.

Ogólna składnia ustawiania klucza obcego na poziomie tabeli jest następująca:

KLUCZ OBCY (kolumna1, kolumna2, ...kolumnaN) REFERENCJE tabela_główna (kolumna_tabeli_głównej1, kolumna_tabeli_głównej2, ...kolumna_tabeli_głównejN)

Aby utworzyć ograniczenie klucza obcego, po KLUCZ OBCY należy określić kolumnę tabeli, która będzie reprezentować klucz obcy. A po słowie kluczowym REFERENCES wskazywana jest nazwa powiązanej tabeli, a następnie w nawiasie nazwa powiązanej kolumny, na którą będzie wskazywał klucz obcy. Po wyrażeniu REFERENCES znajdują się wyrażenia ON DELETE i ON UPDATE, które określają akcję odpowiednio przy usuwaniu i aktualizowaniu wiersza z tabeli głównej.

Na przykład zdefiniujmy dwie tabele i połączmy je za pomocą klucza obcego:

UTWÓRZ TABELĘ Klienci (Identyfikator INT PRIMARY KEY AUTO_INCREMENT, Wiek INT, Imię VARCHAR(20) NIE NULL, Nazwisko VARCHAR(20) NIE NULL, Telefon VARCHAR(20) NIE NULL UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Klienci (Id));

W tym przypadku definiowane są tabele Klienci i Zamówienia. Klienci są najważniejszymi podmiotami i reprezentują klienta. Zamówienia są zależne i reprezentują zamówienie złożone przez Klienta. Tabela Zamówienia jest połączona poprzez kolumnę CustomerId z tabelą Klienci i jej kolumną Id. Oznacza to, że kolumna CustomerId jest kluczem obcym wskazującym kolumnę Id z tabeli Klienci.

Możesz użyć operatora CONSTRAINT, aby określić nazwę ograniczenia klucza obcego:

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT Orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Klienci (Id));

PRZY USUNIĘCIU I AKTUALIZACJI

Za pomocą instrukcji ON DELETE i ON UPDATE można ustawić akcje, które zostaną wykonane w przypadku odpowiednio usunięcia lub modyfikacji powiązanego wiersza z tabeli głównej. Jako akcję można zastosować następujące opcje:

KASKADA: Automatycznie usuwa lub modyfikuje wiersze z tabeli zależnej, gdy powiązane wiersze w tabeli głównej zostaną usunięte lub zmodyfikowane.

SET NULL: Podczas usuwania lub aktualizowania powiązanego wiersza z głównej tabeli ustawia kolumnę klucza obcego na NULL. (W tym przypadku kolumna klucza obcego musi obsługiwać ustawienie NULL)

OGRANICZ: Odrzuca usunięcie lub modyfikację wierszy w tabeli głównej, jeśli w tabeli zależnej znajdują się powiązane wiersze.

BRAK AKCJI: tak samo jak OGRANICZ.

SET DEFAULT: Podczas usuwania powiązanego wiersza z tabeli głównej ustawia kolumnę klucza obcego na wartość domyślną określoną za pomocą atrybutu DEFAULT. Pomimo tego, że ta opcja jest w zasadzie dostępna, silnik InnoDB nie obsługuje tego wyrażenia.

Kaskadowe usuwanie

Usuwanie kaskadowe umożliwia automatyczne usunięcie wszystkich powiązanych wierszy z tabeli zależnej podczas usuwania wiersza z tabeli głównej. W tym celu należy skorzystać z opcji KASKADA:

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCJE Klienci (Id) ON DELETE CASCADE);

Wyrażenie ON UPDATE CASCADE działa w podobny sposób. Kiedy zmieniasz wartość klucza podstawowego, wartość powiązanego z nim klucza obcego zmienia się automatycznie. Ponieważ jednak klucze podstawowe zmieniają się bardzo rzadko i ogólnie nie zaleca się używania jako kluczy podstawowych kolumn ze zmiennymi wartościami, wyrażenie ON UPDATE jest rzadko używane w praktyce.

Ustawienie NULL

Po ustawieniu opcji SET NULL dla klucza obcego kolumna klucza obcego musi mieć dopuszczalną wartość NULL:

CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Klienci (Id) ON DELETE SET NULL);

) przyjrzeliśmy się strukturze relacyjnej (tabelarycznej) bazy danych i odkryliśmy, że głównymi elementami relacyjnej bazy danych są: tabele, kolumny i wiersze, a w ujęciu matematycznym: relacje, atrybuty i krotki. Często wiersze nazywane są rekordami, kolumny nazywane są kolumnami, a przecięcie rekordu i kolumny nazywane jest komórką.

Należy pamiętać, że zawartość wierszy i nazwy kolumn muszą być unikalne w obrębie tej samej bazy danych.

Typy danych w bazach danych

Ważne jest, aby zrozumieć, że możesz tworzyć bazy danych dla dowolnego rodzaju danych: tekstów, dat, godzin, zdarzeń, liczb. W zależności od rodzaju informacji relacyjne bazy danych dzielimy na typy. Każdy typ danych (atrybut) ma swoje własne oznaczenie:

INTEGER - dane z liczb całkowitych;
FLOAT – dane z liczb ułamkowych, tzw. dane zmiennoprzecinkowe;
CHAR, VARCHAR - typy danych tekstowych (znakowe);
LOGICAL — logiczny typ danych (tak/nie);
DATA/CZAS - dane czasowe.

To są główne typy danych, a jest ich w rzeczywistości znacznie więcej. Ponadto każdy język programowania ma swój własny zestaw atrybutów systemowych (typów danych).

Jaki jest klucz podstawowy i klucz obcy tabel relacyjnej bazy danych

Główny klucz

Przypomnieliśmy powyżej: każdy wiersz (rekord) bazy danych musi być unikalny. Jest to klucz podstawowy w postaci zbiorów określonych wartości, który maksymalnie identyfikuje każdy rekord. Można to różnie definiować. Klucz podstawowy: zestaw specyficznych cech, które są unikalne dla każdego rekordu. Klucz podstawowy jest oznaczony jako klucz podstawowy.

Klucz podstawowy (PK) bardzo ważne na każdym stole. Pozwól, że wyjaśnię dlaczego.

Klucz podstawowy nie pozwala na tworzenie identycznych rekordów (wierszy) w tabeli;
PK zapewniają logiczne połączenie pomiędzy tabelami w jednej bazie danych (w przypadku relacyjnych baz danych).

Warto bardziej szczegółowo zastanowić się nad logicznym powiązaniem między tabelami.

Klucz obcy

Klucz obcy, w skrócie FK. Zapewnia jednoznaczne połączenie logiczne pomiędzy tabelami jednej bazy danych.

Przykładowo mamy dwie tabele A i B. W tabeli A (obuwie) znajduje się klucz podstawowy: rozmiar, w tabeli B (kolor) powinna znajdować się kolumna o nazwie rozmiar. W tej tabeli „rozmiar” będzie kluczem obcym dla logicznego połączenia tabel B i A.

Bardziej złożony przykład.

Dwie tabele danych: Osoby i Numery telefonów.

Tabela: Ludzie

Tabela: Numery telefonów

W tabeli Numery telefonów PK jest unikatowa. FK tej tabeli jest PK tabeli People. Połączenie numerów telefonów z osobami zapewniają tablice telefoniczne FK. To jest:

Zaitsev ma dwa telefony;
Volkov ma dwa telefony;
Belkin ma jeden telefon.

klucz podstawowy i klucz obcy

Na zakończenie dodam, że każdy, Menedżer bazy danych ma techniczną możliwość utworzenia klucza podstawowego.

Czytać:

Integracja wirtualnej centrali PBX Zadarma i Zoho CRM Kolorowa muzyka na Arduino Kolorowa muzyka na mikrokontrolerze AVR Co zrobić, jeśli Twój Mac się nagrzewa w systemie Windows Ochłodź MacBooka w systemie Windows Twój Mac zacznie gwałtownie zwalniać, ale można tego uniknąć Jakie są cechy gry na europejskim serwerze Archeage?