Klucz główny
Primary Key
Klucz główny tabeli - primary key - jest to nic innego jak ograniczenie nałożone na pojedynczą kolumnę lub grupę kolumn. Warunkiem koniecznym do założenia tego podstawowego więzu jest unikalność danych w kolumnach wchodzących w jego skład oraz brak pustych wierszy - not null. Klucz primary key jest przede wszystkim podstawowym wyznacznikiem służącym do rozróżniania wierszy w tabeli (niekiedy stosuje się do tego systemową kolumnę rowid - ale to temat na odddzielny artykuł).
Trzeba pamiętać, że może istnieć wyłącznie jeden klucz główny w tabeli bazy danych Oracle.
Na krótko przejdźmy do biznesowej praktyki - według mnie - primary key powinien być całkowicie oddzielony od pojęć biznesowych. Może się tak zdażyć, że mamy w przedsiębiorstwie id klienta, którego używa się w procesach bisnesowych. Wielką pokusą jest wtedy stworzenie klucza głównego właśnie na podstawie w/w id. Jest to niestety wg mnie istotny błąd w projektowaniu. Należy bowiem bezwarunkowo stworzyć zupełnie nowych identyfikator (np. za pomocą sekwencji) na potrzeby klucza głównego. Identyfikator taki nie powinien być znany z poziomu aplikacji. Dlaczego tak uważam? Przede wszystkim dlatego, że potrzeby w biznesie zmieniają się o wiele częściej niż zmieniają zasady działania klucza głównego. Po pewnym czasie może się okazać, że któryś z klientów identyfikatora id nie posiada, jest dwóch różnych klientów o tym samym id lub jest nowy klient jeszcze bez przydzielonego id. Ktoś wtedy na pewno będzie musiał wytłumaczyć "dlaczego nie da się tego zrobić szybko i tanio"...
create table ... primary key
Najprościej klucz główny dodawac przy tworzeniu tabeli. Oto chyba najpopularniejszy sposób:
create table tmp( id number primary key, nazwa varchar(20));
lub nieco inaczej:
create table tmp( id number, nazwa varchar(20), constraint tmp_id_pk primary key (id));
Ale wg mnie najlepiej - najszybciej jest tak:
create table tmp( id number constraint tmp_id_pk primary key, nazwa varchar(20) );
w/w wersja jest pewną przewagę nad wersją pierwszą i drugą ponieważ jawnie nazywa więzy. To pomaga w następnych fazach życia aplikacji - szczególnie administratorom.
Klucz jest bowiem obiektem na bazie i właśnie przez jego nazwę należy się do niego odwoływać - jeśli będzie to coś w stylu SYS_C004905 w kilkustet kolejnych wierszach to możemy się boleśnie pomylić.
Należy przy okazji zauważyć, że klucze (constraints) muszą mieć unikalne nazwy w danym schemacie bazy danych.
Alter table ... add constraint ... primary key
Istnieje możliwość dodania klucza głównego - primary key - na długo pod stworzeniu tabeli. Mimo to polecam zrobić to odrazu przy tworzeniu tabeli. Później może wystąpić sytuacja gdy będzie to trudne albo nawet niemożliwe bez utraty części danych.
alter table moja_tablela add constraint nazwa_klucza_glownego_pk primary key(nazwa_kolumny);
lub dla wielokolumnowego - złożonego klucza primary key /* Oracle posiada ograniczenie - max 32 kolumny mogą zostać użyte jako pojedynczy primary key */
alter table moja_tablela add constraint nazwa_klucza_primary_key primary key(kolumna1, kolumna2, ..., kolumna32);
*konwencja głosi że nazwy kluczy itp należy nadawać z sensem np dodawać na jego końcu czy początku '_pk' - nie jest to oczywiście obowiązek!
Alter table ... drop constraint
Usuwanie klucza głównego (primary key) z dowolnej tabeli na bazie Oracle jest równie proste:
alter table nazwa_tabeli drop constraint nazwa_klucza_primary_key;
Jest jednak co najmniej kilka powodów by nie usuwać kluczy z bazy. Tu chciałbym przypomnieć o specyficznym zachowaniu klucza primary key. W momencie tworzenia klucza głownego automatycznie i niejawnie zostaje stworzony index o nazwie więzu na kolumnie/kolumnach wchodzących w jego skład. Można to sprawdzić w perspektywach:
select * From user_indexes where table_name = trim(upper('&table'));
select * from user_constraints where table_name = trim(upper('&table'));
Co w/w indeksy dają w praktyce - wyszukiwanie danych wg kolumn klucza głównego zostaje bardzo przyspieszone. Usunięcie klucza spowoduje usunięcie indeksu i na 100% drastycznie zwiększy koszt select-a. Jest przy tym duża szansa że przy usuwaniu klucza zapomnimy stworzyć odpowiedni index. A jeśli np zapytanie nie zwróci wyniku przez powiedzmy 120s - może wygasnąć sesja użytkownika aplikacyjnego.
Więzy vs indeksy
Rozwiązanie tego problemu jest całkiem banalne. Należy jawnie tworzyć indeksy przy tworzeniu więzów
create table tmp3( p1 number(2), p2 number(2), d varchar2(40), constraint tmp3_pk primary key (p1, p2) using index (create unique index tmp3_pk_idx on tmp3(p1, p2)));
lub
alter table tmp3 add constraint tmp_pk primary key (d) using index (create unique index tmp_uq on tmp3(d)); /* tu ciekawostka - można w ten sposób założyć index non-unique dla klucza primary key! */
Sprawdziłem to (Oracle 10g) i niestety w/w sposób nie pomaga w sytuacji drop/disable constraints - kiedy to kasowane są automatycznie powiązane indeksy. Lepiej jest wykonywać wszystkie operacje całkiem oddzielnie.
create unique index tmp_uq on tmp3(d); alter table tmp3 add constraint tmp_uq unique (d) using index tmp_uq;
W ten sposób polecenie alter table ... drop constraint albo alter table ... disable nie usunie już automatycznie indeksu wchodzącego w skład klucza. Huraaa.........!
Alter table ... disable/enable constraint
Usunięcie klucza primary key na ogół nie ma większego sensu. Klucze na bazie to podstawa bezpieczeństwa (zachowanie spójności danych) - jeśli już koniecznie trzeba wykonać coś nietypowego na bazie danych, co jest sprzeczne z założeniami klucza głównego można taki klucz na jakiś czas wyłączyć:
alter table tmp disable constraint tmp_pk;
by po wykonaniu zmian na bazie można go było włączyć:
alter table tmp enable constraint tmp_pk;
W/w operacje wyłączania czy usuwania klucza głównego nie powiodą się jeśli klucz ten związany jest z "zewnątrz" jakimś kluczem obcym foreign key. Można oczywiście sprawdzić czy tak nie jest unikając próby "na żywo".
select * from user_constraints where r_constraint_name = 'OSOBA_PK';
Poza tym prawie na 100% po jakimś czasie zapomnimy które klucze zostały w podobny sposób zmodyfikowane więc trzeba umieć to sprawdzić. Umożliwia to zapytanie wyświetlające dane z widoków systemowych
select constraint_name, status from user_constraints -- all_constraints -- dba_constraints where status = 'DISABLED';
Innym widokiem systemowym przechowującym dane o kluczach jest user_cons_columns - tu znaleźć można tabele/kolumny na których założone sa jakieś więzy.
select * from user_cons_columns where table_name = upper(trim('&table_name'));
Niestety może się okazać, że klucza nie da się włączyć ze względu na "jakość" danych - wtedy można zastosować pewne modyfikacje klucza... zapraszam na stronę dotyczącą parametrów kluczy
Primary key
Klucz główny tabeli (primary key) to nic innego jak połączenie kolumny z indeksem oraz więzów: not null i unique. Jest jednak istotna różnica - może istnieć wyłącznie jeden klucz primary key na tabeli a więzów unique / not null możemy założyć dowolnie dużo.
Data ostatniej modyfikacji wpisu: 2009.11.21 14:43:45.