sql server - blokada przed usuwaniem powiązanych rekordów

Załóżmy że mamy dwie tabele z których pierwsza to Users mająca pola ID (int) i Nazwisko (varchar) oraz klucz główny jako pole pierwsze czyli ID, natomiast druga to Components mająca pola ID (int), UserID (int) i Nazwa (varchar) oraz klucz główny jako pierwsze pole czyli ID.
Załóżmy też że obie tabele powiązane są taką relacją że kluczem obcym tabeli Components jest pole ID tabeli Users.

Czy można jakoś skonfigurować bazę tak aby nie było możliwości usuwania User'a z ID które już widnieje w tabeli Components.
Wiem że są reguły relacji (on delete i on update) ale one mi średnio pomagają bo definiuje się tam zachowania co ma się stać już po usunięciu/zmienieniu, a ja do tego usunięcia po prostu nie chcę w żadnym razie dopuścić.

Pomocy proszę.

Miałbym jeszcze jedno pytanie ale mniej priorytetowe: mianowicie jeśli w folderze database diagrams sqlstudia stworzę diagram i określę relacje kluczy pomiędzy bazami to tak naprawdę na co to ma wpływ. Przecież to i tak aplikacja (np. z poziomu c#) będzie wykrywać Usera i jego ID'kiem zapełniać pole UserID tabeli Components i nie potrzebujemy do tego żadnych "relacji", wystarczą dwie tabele. To na co tak naprawdę mają wpływ te "relacje" czyli powiązania kluczy?
I co znaczy opcja przy tworzeniu relacji o nazwie "enforce foreign key constraints", może mi to ktoś przystępnie wyjaśnić bo nie bardzo mam czas o tym teraz czytać bo muszę niedługo oddać system do użytku...

masz w foreign key zdefiniowane ON DELETE CASCADE. Usunięcie tego powinno blokować usunięcie rekordów-matki.

Z moich obserwacji wynika że On delete cascade to opcja która usuwa rekordy dzieci jeśli usuniemy rekord matki i to zgodzę się że należy przestawić.

Opcji tej nie usuwa się, można jedynie przestawić na coś innego z listy:

• On delete Cascade
• No action
• Set null
• Set default

Jednak to co piszesz to dokładnie to co chcę osiągnąć. Czyli że chcę zablokować przed usunięciem rekordów matki wówczas gdy są one używane w innych tabelach.
Które mam wybrać żeby działało jak piszę, wiesz może od ręki ?

NO ACTION - to domyślne ustawienie (nie trzeba go podawać pisząc constraint z palca), które blokuje usuwanie rekordów, które mają rekordy dzieci.

Dziękuję za odpowiedzi. Działa to tak jak piszesz, właśnie testowałem, o to mi chodziło, sam nie wiem czemu tego nie sprawdziłem wcześniej ;)
PS. Fajne masz zdjęcie profilowe, naprawdę.

Panowie miałbym jeszcze takie pytanie, czy da się jakoś ustawić aby nie było możliwości dodania do tabeli rekordu dla którego kolumna (przykładowo typu text) nie będąca kluczem (czyli jedna z najzwyklejszych) miałaby taką samą treść jak dla innego rekordu tej tabeli, innymi słowy: aby była unikalna.. ?

Dodam tylko że zakładając klucz główny mam możliwość ustawienia opcji IsUnique jednak opcji tej nigdzie nie widzę w odniesieniu do kolumn które nie są skojarzone z żadnym z kluczy...

To się nazywa Unique Constraint, a zakłada się go w ten sposób:

ALTER TABLE UserTable 
ADD CONSTRAINT AK_Password UNIQUE (Password);

W podanym powyżej przykładnie do istniejącej tabeli "UserTable" dołożyłem unique contraint na pole "Password", sprawiając w ten sposób, że nie będzie możliwości wstawienia dwóch takich samych wartości w tym polu.

Zamykam temat, bardzo mi pomogłeś, wszystko działa jak należy.
Dodam tylko że zaproponowane przez Ciebie instrukcje można wykonać nawet wówczas gdy w danej tabeli są już jakieś rekordy, nie trzeba tego robić na etapie tworzenia tabeli, no chyba że już w istniejącej tabeli na którą zamierzamy nałożyć tego typu warunki są rekordy z powtarzającymi się wartościami w tej danej kolumnie.
Jeszcze raz dziękuję i pozdrawiam ciepło!

A czy jest jakiś sposób żeby korzystając z SQL Studio sprawdzić sobie jakie ograniczenia zostały narzucone na daną kolumnę, po kliknięciu na design danej tabeli i zaznaczeniu danej kolumny nigdzie nie widzę takiej opcji... ?

Jest na to co najmniej kilka sposobów.

1. W trybie DESIGN klikasz prawym i wybierasz Indexes/Keys i przeglądasz sobie po kolei wszystkie klucze, indeksy poszukując w "Columns" swojej kolumny.
2. Używasz wbudowanej funkcji

sp_help 'nazwa_tabeli'

która to pokazuje wiele szczegółów na temat tabeli, łącznie z wylistowaniem kolumn, indeksów, kluczy itp.
3) Robisz query na tabelkach systemowych:

SELECT kc.name, kc.type
FROM sys.key_constraints kc
INNER JOIN sys.columns c ON c.object_id = kc.parent_object_id
INNER JOIN sys.tables t ON t.object_id = kc.parent_object_id
WHERE t.name = 'nazwa_tabeli' AND c.name = 'nazwa_kolumny';

Niestety tabelek z constraintami jest kilka, klucze są w sys.key_constraints, wartości domyślne w sys.default_constraints, a check w sys.check_constraints

Chciałbym zapytać odnośnie Logins and Users. Jak się dowiedziałem Logins przechowują dane logowania na serwer, Users - do bazy konkretnej.
Jak jednak osiągnąć coś takiego żeby stworzyć sobie jednego Usera np. o nazwie reader i nadać mu uprawnienia tylko do odczytu tabel danej bazy a później w miarę powoływania na bieżąco nowych loginów kojarzyć je z tym właśnie Userem. Dowiedziałem się że relacja jest jeden do jednego (mapowanie) jeśli mowa jest tylko o jednej bazie danych więc czy każdorazowo po stworzeniu logina muszę tworzyć Usera i nadawać mu te same uprawnienia tylko do odczytu ?

I jeszcze takie pytanie: czemu w definiowaniu Usera mamy do dyspozycji aż 5 zakładek: general, owned schemas, membership, securables, extended prop.
W zakładce generals jest pole default schema, podczas gdy w zakładce owned schemas możemy wybrać uprawnienia, dodatkowo w zakładce membership mam to samo co w zakładce owned schema. Po co tyle tego tam jest ? Mógłby mi ktoś w skrócie powiedzieć na co wpływ ma ustawienia w tych zakładkach: general/default schema, owned schema i membership.

Please help me!

Login to identyfikator użytkownika na poziomie całego serwera, z kolei user to identyfikator na poziomie jednej bazy. Loginami zarządzasz z poziomu

nazwa_serwera -> security -> logins

z kolei userami zarządzasz z poziomu

nazwa_serwera -> databases -> nazwa_bazy -> security -> users

Wejdź tam i zmień nazwę usera, wtedy przy mapowaniu loginu z userem będziesz musiał podać zmienioną nazwę usera, jak podasz nazwę loginu, to Cię opluje.

Mieszasz trochę userów z rolami. To role decydują o uprawnieniach. Masz role dla loginów np. serveradmin oraz role dla userów np. db_datareader.

No dobra, to co napisałeś to doskonale rozumiem. Co to jednak jest schema i po co to jest w ogóle ?
I czemu jak sobie tworzę Usera mam aż 5 zakładek: general, owned schemas, membership, securables, extended prop.
Głównie chodziłoby mi o to co powinienem wpisywać w pole "default schema", jak ustawiać właściwości zakładek owned schema i membership ?
Na co to ma w ogóle wpływ? Czemu powtórzone mam w owned schema to samo co w membership, nie kapuję...
I wreszcie dlaczego jak edytuję sobie schema (np. dbo) z poziomu bazy to tam nic nie ma wypełnionego, jakby wszystkie schemas są czyściutkie, to po co tam w ogóle są jeśli nic nie definiują?

Czym dokładnie jest role a czym schema ? Wiem że jest milion wypowiedzi na ten temat, ale jednak proszę o kilka zdań podpowiedzi.
Twoje posty bardzo mi pomagają, dużo bardziej niż inne artykuły, stąd prośba...

Schema przypomina namespace z C# albo package z Java. Schema to logiczna grupa dla obiektów bazodanowych. Przykładowo możesz założyć sobie schema "Pracownik" i wszystkie tabele dotyczące pracowników tam wrzucić. Następnie do tej schema możesz nadać uprawnienia tak aby dany użytkownik miał dostęp tylko do niej.

Default schema decyduje o tym, która schema będzie domyślna gdy użytkownik wykona jakieś zapytanie bez jej podawania. Przykładowo jeżeli użytkownik wpisze

SELECT * FROM Adres

a jako domyślną schema będzie miał "Pracownik" to baza będzie szukała tabeli z tej schema czyli wykona się tak naprawdę

SELECT * FROM Pracownik.Adres

z kolei jak będzie miał domyślną schema "dbo" to baza będzie szukała tabeli dbo.Adres, a to może być już inna tabela, ponieważ nazwy tabel mogą być takie w ramach różnych schema czyli możesz mieć tabelę Adres w schema "Kontrachenci" i zupełnie inną tabelę Adres w schema "Pracownicy" i jeszcze inną w schema "dbo".

No dobrze, jasna sprawa. Mógłbyś mi napisać jeszcze tylko coś na temat tego co oferują nam zakładki owned schema i membership przy operacji tworzenia Usera ?
Dla uściślenia chodzi mi o to co oferuje zakładka owned schema jeśli przecież schema tworzy się z z poziomu security/schemas/new schema... ?
Czy to jest tak że jak w owned schema będą zaznaczone pewne schematy to ten user może tych schematów w jakiś sposób używać ?? Jeśli tak to czy mógłbyś mi opisać jeszcze jakiś jeden przykład użycia jakiegoś schematu...? Nie bardzo to jeszcze kapuję...
Dodatkowo podejrzewam że w membership oznacza się te role (zestawy uprawnień) które chcemy aby nałożone były na tego Usera, czy tak?

Poza powyższym mam jeszcze takie pytanie: Załóżmy że na poziomie roli chcemy coś zmienić (zablokować wykonywanie czegoś tam) ale załóżmy też że rola ta jest używana dla wielu użytkowników i teraz wiadomo że jeśli zmienię coś w roli to wszyscy użytkownicy to także zmienią. A co jeśli chciałbym pozostawić to prawo tylko dla wybranego Usera który także tą rolę używa?
Muszę tworzyć osobną rolę na wzór pierwotnej i zamiast tej pierwotnej temu Userowi przypisać nowo utworzoną?

W Owned Schemas możesz ustawiać czy user będzie właścicielem któregoś z istniejących schematów. To sprawi, że nie będzie można dropnąć danego usera, do czasu gdy istnieją obiekty w danej schema. Z kolei w Membership możesz ustawić czy user będzie miał jakieś role. Role tak jak pisałem wcześniej dają uprawnienia. W tym miejscu warto sobie zapamiętać, że login też może mieć role i uprawnienia idą w dół czyli jeżeli login będzie miał jakieś uprawnienia, to user stworzony dla tego loginu również będzie miał te same uprawnienia, ponieważ odziedziczy je po loginie.

Jasna sprawa. Mam jeszcze takie pytanie : przy edytowaniu schema domyślnego dbo w zakładce general pojawia się pole schema owner i jak kliknę search to on mi pozwala browse'ować po rolach co by oznaczało że to role'a może być ownerem schema'y ... jaki to ma sens ?

Zerkając zaś do zakładki permission mamy tam okienko o nazwie "user or roles" do którego mogę "wrzucić" jak sama nazwa wskazuje Usera albo rolę. Jaki ma sens ustawianie uprawnień schematu jako użytkownika ? Ustawianie uprawnień schematu jako role to rozumiem bo role to zestaw tych uprawnień właśnie...

Jak wykonać taką operację aby dana tabela przynależąca do schematu powiedzmy dbo znalazła się zamiast w dbo to w innym schema powiedzmy o nazwie userschema...?

i jaki sens ma role o nazwie : public ?

Please HELP!

Mam takie jeszcze pytanie: czy jak mam utworzone schema zawierającego parę tabel to jak zrobić aby część użytkowników miała uprawnienia tylko do odczytu tych tabel, część z nich pełne prawa a część z nich żadnych praw. Do schema bowiem mogę wrzucić oprócz tabel userów i roles ale jak zrobić aby niektórzy z userów mieli jedne zestawy uprawnień (roles) a niektórzy inne jeśli przecież ja tam mogę wrzucić wszystko razem - skąd więc system będzie wiedział że mamy taki a nie inny podział... ?

Uprawnienia do odczytu można prosto nadać w ten sposób:

GRANT SELECT ON SCHEMA :: <schema_name> TO <user_name>;

Uprawnienia wszystkie w ten sposób:

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: <schema_name> TO <user_name>;

Jaki sens ma że "ownerem" schema może być role? Możecie podać jakiś przykład wykorzystania ?
I jaki jest sens ustawiania permission schematu (schema) na danego usera ? Tu także poproszę o przykład wykorzystania..

Zauważyłem również taką opcję przy tworzeniu usera bazodanowego że istnieje możliwość utworzenia usera bez loginu. Jaki jest tego sens skoro przecież tak czy siak ktoś (user) aby móc skorzystać z bazy danych musi się zalogować na silnik? Inaczej: jak ten user bez loginu może wykazać w ogóle swoją obecność ?