SQLSHACK (Polski)

20 lutego, 2021 admin 0 Comments

w poprzednim artykule powszechnie używane ograniczenia SQL Server: NOT NULL, unique i PRIMARY KEY, opisaliśmy szczegółowo trzy pierwsze typy ograniczeń SQL Server; NOT NULL, UNIQUE i PRIMARY KEY. W tym artykule omówimy pozostałe trzy ograniczenia: klucz obcy, czek i domyślne, opisując każdy z nich krótko i podając praktyczne przykłady.,

ograniczenie klucza obcego

klucz obcy jest kluczem bazy danych, który jest używany do łączenia dwóch tabel ze sobą. Ograniczenie klucza obcego identyfikuje relacje między tabelami bazy danych, odwołując kolumnę lub zestaw kolumn w tabeli podrzędnej, która zawiera klucz obcy, do kolumny klucza głównego lub zestawu kolumn w tabeli nadrzędnej.

związek między tabelami potomnymi i nadrzędnymi jest utrzymywany przez sprawdzenie istnienia wartości kluczy obcych tabeli podrzędnej w głównym kluczu referencyjnej tabeli nadrzędnej przed wstawieniem tych wartości do tabeli podrzędnej., W ten sposób ograniczenie klucza obcego w tabeli podrzędnej, która odwołuje się do klucza głównego w tabeli nadrzędnej, wymusi integralność odniesienia do bazy danych. Integralność odniesienia zapewnia zachowanie relacji między tabelami bazy danych podczas procesu wstawiania danych. Przypomnijmy, że ograniczenie klucza głównego gwarantuje, że do tej tabeli nie zostaną wstawione żadne wartości NULL lub duplikaty dla wybranej kolumny lub kolumn, wymuszając integralność encji dla tej tabeli., Integralność jednostki wymuszona przez klucz podstawowy i integralność odniesienia wymuszona przez klucz obcy tworzą razem integralność klucza.

ograniczenie klucza obcego różni się od ograniczenia klucza podstawowego tym, że można utworzyć tylko jeden klucz podstawowy dla każdej tabeli, z możliwością tworzenia wielu ograniczeń klucza obcego w każdej tabeli poprzez odwoływanie się do wielu tabel nadrzędnych. Inną różnicą jest to, że klucz obcy umożliwia wstawianie wartości NULL, jeśli nie ma ograniczenia nie NULL zdefiniowanego na tym kluczu, ale klucz podstawowy nie akceptuje wartości null.,

ograniczenie klucza obcego zapewnia również możliwość kontrolowania, jakie działania zostaną podjęte, gdy odwołana wartość w tabeli nadrzędnej zostanie zaktualizowana lub usunięta, za pomocą klauzul ON UPDATE i on DELETE. Obsługiwane akcje, które można wykonać przy usuwaniu lub aktualizowaniu wartości tabeli nadrzędnej, to:

  • NO ACTION: gdy klauzule ON UPDATE lub on DELETE są ustawione na NO ACTION, operacja aktualizacji lub usuwania w tabeli nadrzędnej zakończy się błędem.,
  • CASCADE: ustawiając klauzule on UPDATE lub on DELETE na CASCADE, ta sama akcja wykonywana na odwołanych wartościach tabeli nadrzędnej zostanie odzwierciedlona do powiązanych wartości w tabeli podrzędnej. Na przykład, jeśli odwołana wartość zostanie usunięta w tabeli nadrzędnej, wszystkie powiązane wiersze w tabeli podrzędnej zostaną również usunięte.
  • Ustaw NULL: z tą opcją w klauzulach UPDATE I DELETE, jeśli wartości odniesienia w tabeli nadrzędnej zostaną usunięte lub zmodyfikowane, wszystkie powiązane wartości w tabeli podrzędnej zostaną ustawione na wartość NULL.,
  • SET DEFAULT: użycie opcji SET DEFAULT klauzul ON UPDATE i on DELETE określa, że jeśli wartości odniesienia w tabeli nadrzędnej zostaną zaktualizowane lub usunięte, powiązane wartości w tabeli podrzędnej z kolumnami klucza obcego zostaną ustawione na wartość domyślną.

możesz dodać ograniczenie klucza obcego podczas definiowania kolumny za pomocą instrukcji CREATE TABLE T-SQL lub dodać je po utworzeniu tabeli za pomocą instrukcji ALTER TABLE T-SQL. Stworzymy dwie nowe tabele, aby zrozumieć funkcjonalność ograniczenia klucza obcego., Pierwsza tabela będzie działać jako tabela nadrzędna z kolumną ID zdefiniowaną jako kolumna klucza głównego. Druga tabela będzie działać jako tabela potomna, z kolumną ID zdefiniowaną jako kolumna klucza obcego, która odwołuje się do kolumny ID w tabeli nadrzędnej.,

Name VARCHAR(50) NULL
)
GO
CREATE TABLE ConstraintDemoChild
(
CID INT PRIMARY KEY,
id Int FOREIGN KEY REFERENCES ConstraintDemoParent(ID)
)

Po utworzeniu dwóch tabel wstawimy trzy rekordy do tabeli nadrzędnej, a dwa rekordy do tabeli podrzędnej, używając następujących poleceń insert:

wynik pokaże, że trzy rekordy zostały pomyślnie wstawione do tabeli nadrzędnej., Pierwszy rekord, który próbowaliśmy wstawić do tabeli potomnej, jest wstawiany bez żadnego błędu, ponieważ wartość ID 1 już istnieje w tabeli nadrzędnej.,ponieważ wartość ID 4 nie istnieje w tabeli nadrzędnej, a z powodu ograniczenia klucza obcego, nie będziesz mógł wstawić wartości ID do tabeli podrzędnej, która nie istnieje w tabeli nadrzędnej:

sprawdzanie zawartości tabel nadrzędnych i podrzędnych, zobaczysz, że tylko jeden rekord jest wstawiany do tabeli podrzędnej, jak widać poniżej:

ponieważ nie wspomnieliśmy o nazwie ograniczenia klucza obcego podczas tworzenia tabeli podrzędnej, SQL Server przypisze jej unikalną nazwę, którą możemy pobrać z information_schema.,TABLE_CONSTRAINTS system view using the following query:

1
2
3
4
5
6
7
8
SELECT CONSTRAINT_NAME,
TABLE_SCHEMA ,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.,onstraintDemoChild
dodaj ograniczenie FK__ConstraintDe__ID
referencje klucza obcego (ID) ConstraintDemoParent(ID);

operacja nie powiedzie się, ponieważ wartość ID 4 nie istnieje w tabeli nadrzędnej i łamie referencyjną integralność między tabelami podrzędnymi i nadrzędnymi, co widać w następującym komunikacie o błędzie:

aby móc utworzyć ograniczenie klucza obcego w tabeli podrzędnej, musimy najpierw wyeliminować ten konflikt poprzez usunięcie lub aktualizację tego rekordu., Here we will try to modify the ID value with the following UPDATE command:

1
2
3
UPDATE ., SET ID =2 WHERE ID = 4

teraz ograniczenie klucza obcego zostanie utworzone pomyślnie bez żadnych błędów, jak pokazano poniżej:

A ograniczenie klucza obcego można zdefiniować za pomocą narzędzia SQL Server Management Studio. Kliknij prawym przyciskiem myszy na wymaganej tabeli i wybierz opcję Projekt.,> Z wyświetlonego okna można łatwo określić nazwę ograniczenia klucza obcego, tabele i kolumny, które będą uczestniczyć w tej relacji, klikając specyfikację tabel i kolumn, jeśli wymagane jest sprawdzenie istniejących danych, a na koniec akcję wykonywaną, gdy odwołany rekord jest usuwany lub modyfikowany w tabeli nadrzędnej, jak pokazano poniżej:

ograniczenie wyboru

ograniczenie wyboru jest zdefiniowane na liście kontrolnej.kolumna lub zestaw kolumn w celu ograniczenia zakresu wartości, które można wstawić do tych kolumn, używając predefiniowanego warunku., Ograniczenie CHECK wchodzi w działanie w celu oceny wstawionych lub zmodyfikowanych wartości, gdzie wartość spełniająca warunek zostanie wstawiona do tabeli, w przeciwnym razie operacja insert zostanie odrzucona. Dozwolone jest określenie wielu ograniczeń sprawdzania dla tej samej kolumny.

Definiowanie warunku check constraint jest w jakiś sposób podobne do zapisu klauzuli WHERE w zapytaniu, używając różnych operatorów porównania, takich jak AND, OR, BETWEEN, In, LIKE I IS NULL do napisania wyrażenia logicznego, które zwróci TRUE, FALSE lub UNKNOWN., Ograniczenie CHECK zwróci nieznaną wartość, gdy w warunku jest obecna wartość NULL. Ograniczenie sprawdzania jest używane głównie do wymuszania integralności domeny poprzez ograniczanie wstawianych wartości do tych, które są zgodne z zdefiniowanymi wartościami, zakresem lub regułami formatu.

stwórzmy nową prostą tabelę, która ma trzy kolumny; kolumnę ID, która jest traktowana jako klucz główny tej tabeli, nazwy i wynagrodzenia. Ograniczenie kontrolne jest zdefiniowane w kolumnie wynagrodzenia, aby upewnić się, że do tej kolumny nie są wstawiane żadne wartości zerowe lub ujemne.,v>6

7
8
9
CREATE TABLE ConstraintDemo4
(
ID INT PRIMARY KEY,
name varchar(50) null,
pensja int check (pensja>0)
go

jeśli wykonasz poniższe trzy polecenia insert:

z wygenerowanego wyniku wynika, że pierwszy rekord zostanie wstawiony bez błędu, ponieważ podana wartość wynagrodzenia spełnia warunek sprawdzania.,wyrównane wartości wynagrodzenia nie spełniają warunku ograniczenia kontroli z powodu Wstawienia zerowych i ujemnych wartości wynagrodzenia, co widać w następującym komunikacie o błędzie:

sprawdzanie danych tabeli pokazuje, że tylko pierwszy wiersz, który przeszedł warunek ograniczenia kontroli, zostanie wstawiony do tabeli jak poniżej:

wspomnieliśmy wcześniej w tym artykule, że ograniczenie check pozwala na wstawianie wartości Null, jeśli partycypowane kolumny zezwalają na NULL, a wartości null zostaną ocenione jako Nieznane bez rzucania żadnych błędów., Jest to jasne z poniższego rekordu, który został pomyślnie wstawiony, chociaż podana wartość kolumny wynagrodzenia jest NULL:

jeśli przejrzysz definicję ograniczenia CHECK w poprzedniej instrukcji CREATE TABLE, zobaczysz, że nie podaliśmy nazwy zdefiniowanego ograniczenia. W takim przypadku SQL Server przypisze unikalną nazwę dla tego ograniczenia, którą można wyświetlić poprzez zapytanie INFORMATION_SCHEMA.Widok systemowy TABLE_CONSTRAINTS dla tabeli ConstraintDemo4., Wynik będzie taki, jak pokazano poniżej:

ograniczenie wyboru można usunąć za pomocą polecenia ALTER TABLE T-SQL.,onstraint now, with the following ALTER DATABASE … ADD CONSTRAINT T-SQL statement:

1
2
3
4
5
ALTER TABLE ConstraintDemo4
ADD CONSTRAINT CK__Constrain__Salar
CHECK (Salary>0);

Adding the CHECK constraint will fail., Wynika to z faktu, że podczas gdy serwer SQL sprawdza już istniejące dane pod kątem warunku check constraint, znaleziono jedną lub więcej wartości, które nie spełniają warunku check constraint, jak widać w następującym komunikacie o błędzie:

aby móc ponownie zdefiniować ograniczenie CHECK, powinniśmy zmodyfikować lub usunąć dane, które uniemożliwiają utworzenie ograniczenia.,de484761″>

1
2
3
Update ConstraintDemo4 SET pensja =300 WHERE ID IN (2,3)

teraz ograniczenie sprawdzania można zdefiniować bez problemu, jak pokazano poniżej:

SQL Server pozwala wyłączyć ograniczenie sprawdzania w specjalnych przypadkach, takich jak wstawianie ogromnej ilości danych danych bez dbania o spełnienie warunku ograniczenia w celach testowych lub ze względu na zmianę logiki biznesowej., Ten przypadek dotyczy tylko ograniczeń CHECK i FOREIGN KEY, które można tymczasowo wyłączyć.,zmiana nazwy ograniczenia, jak w poleceniu T-SQL poniżej:

1
2
3

z definicji ograniczenia wyboru można określić, że ograniczenie zostanie utworzone bez sprawdzania istniejących wierszy, ale ograniczenie będzie oznaczone jako nie zaufane., można włączyć wszystkie ograniczenia sprawdzania, wszystkie na raz,za pomocą polecenia T-SQL poniżej:

1
2
3

podczas włączania poprzedniego ograniczenia sprawdzania, zobaczysz, że SQL Server nie będzie narzekał na niezaznaczone dane, które łamią warunek ograniczenia., W takim przypadku integralność domen danych nie jest utrzymywana.

aby rozwiązać ten problem, polecenie DBCC CHECKCONSTRAINTS można łatwo użyć do identyfikacji danych, które naruszają warunek ograniczenia w określonej tabeli lub ograniczeniu, biorąc pod uwagę, że nie należy uruchamiać tego polecenia w godzinach szczytu, ponieważ wpłynie to na wydajność SQL Server z powodu braku korzystania z migawki bazy danych.,v id=”ff419aa09d”>

1
2
3

DBCC checkconstraints(constraintdemo4)

wynik wyświetli dwie wartości wynagrodzenia, które naruszają warunek ograniczenia kontroli, jak pokazano poniżej:

ograniczenie wyboru można również utworzyć za pomocą narzędzia SQL Server Management Studio, klikając prawym przyciskiem myszy na wymaganej tabeli i wybierając opcję projekt., W wyświetlonym oknie projektu kliknij prawym przyciskiem myszy i wybierz opcję sprawdź ograniczenia w następujący sposób:

w oknie sprawdź ograniczenia możesz określić nazwę ograniczenia sprawdź, wyrażenie i jeśli sprawdzenie istniejących danych jest wymagane, jak pokazano poniżej:

domyślne ograniczenie h2>

domyślne ograniczenie jest używane do podania domyślnej wartości kolumny dla wstawionych wierszy, jeśli w instrukcji INSERT nie podano wartości dla tej kolumny., Domyślne ograniczenie pomaga w utrzymaniu integralności domeny, zapewniając odpowiednie wartości dla kolumny, na wypadek, gdyby użytkownik nie podał dla niej wartości. Wartością domyślną może być wartość stała, wartość funkcji systemowej lub NULL.,elow:

1
2
3
4
ALTER TABLE constraintdemo5
dodaj constraint DF__constrain__emplo default (getdate()) dla emploeedate

również domyślne ograniczenie można zdefiniować za pomocą SQL Server Management Studio, klikając prawym przyciskiem myszy na wymaganej tabeli i wybierając opcję projekt., Następnie wybierz kolumnę, dla której przypisasz wartość domyślną, przeglądając okno Właściwości kolumny, jak pokazano poniżej:

miałem nadzieję, że ten artykuł i poprzedni pomogły wyjaśnić sześć typów ograniczeń SQL Server. Prosimy o zadawanie pytań w komentarzach poniżej.,nly używane ograniczenia serwera SQL: nie NULL, unikalny i klucz podstawowy

Przydatne linki

  • Tworzenie ograniczeń sprawdzania
  • ograniczenia
  • domyślne
  • Tworzenie zależności kluczy obcych
  • ograniczenia sprawdzania

  • autor
  • ostatnie posty
,
jest Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
także swoje porady SQL ' owe wnosi na wielu blogach.,
Zobacz wszystkie posty Ahmad Yaseen

najnowsze posty Ahmad Yaseen (Zobacz wszystkie)
  • Jak monitorować Azure Data Factory – 15 stycznia 2021 r.
  • za pomocą kontroli źródła w Azure Data Factory – korzystanie z szablonów w usłudze Azure Data Factory – 8 stycznia 2021 r.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *