Spis treści:

Kategoria:SQL Server


Dodawanie tabel w SQL Server

Wszystko zaczyna się od tabeli

Relacyjne bazy danych kojarzą się większości z tabelami i kolumnami. Bardziej dociekliwi zauważają jeszcze ralacje (w końcu to relacyjne bazy danych), jeszcze inni wpomną coś o indeksach. Prawdziwi ekstremiści nawiążą coś transakcjach, grupach plików, replikacji. Dlaczego tabele i kolumny są tak ważne? Bo to tam spoczywają wszystkie dane. O to przecież głównie o dane nam chodzi. Postanowiłem zatem zebrać, także dla siebie, najczęściej stosowane konstrukcje wykorzystywane przy tworzeniu tabel.

Tabela bez żadnych specjalnych zdolności

Tabela z dwoma kolumnami różnych typów, obie kolumny mogą przyjmować wartość NULL:

CREATE TABLE Tabela
(
  Kolumna1 int,
  Kolumna2 varchar(4)
)

Tabela z całkowitym kluczem głównym

Tabela ze sztucznym, całkowitym kluczem głównym z dwoma dowolnymi kolumnami. W tym przypadku nie ma możliwości dodania dwóch takich samych rekordów. Będą się różniły przynajmniej tym właśnie kluczem - SQL Server nie dopuści do wstawienia dwóch rekordów o tym samym kluczu, zatem i cały rekord pozostanie unikatowy. Zobacz też Usuwanie duplikatów z tabeli bez klucza - SQL Server.

CREATE TABLE TabelaZKluczem
(
  Kolumna1 int,
  Kolumna2 varchar(4),
  CONSTRAINT PK_Klucz_Unikalny
  PRIMARY KEY (Kolumna1)
)

--Nie da się dodać dwóch rekordów z takim samym kluczem
--Msg 2627, Level 14, State 1, Line 1
--Violation of PRIMARY KEY constraint 'PK_Klucz_Unikalny'. Cannot insert duplicate key in object 'dbo.TabelaZKluczem'. The duplicate key value is (1).
--INSERT INTO TabelaZKluczem VALUES (1,'AAAA'),(1,'BBBB')

Tabela z kluczem głównym IDENTITY

Opcja IDENTITY pozwala na automatyczne zwiększanie identyfikatora rekordu (zobacz Identity jako identyfikator). To dość powszechna technika, bo automatycznie zapewnia unikatowość rekordu. Klucz główny jest liczbą całkowitą, dlatego bardzo łatwo potem wykonywać złączenia.

CREATE TABLE TabelaZIdentity
(
  ID int IDENTITY,
  Kolumna1 decimal(9,2),
  Kolumna2 nvarchar(30),
  CONSTRAINT PK_TabelaZIdentity_ID
  PRIMARY KEY (ID)
)

INSERT INTO TabelaZIdentity VALUES
(4.5, N'Cztery i pół'),
(105, N'Sto pięć')

SELECT ID, Kolumna1, Kolumna2 FROM TabelaZIdentity

Wykonanie instrukcji SELECT zwróci następujące wyniki:

IDKolumna1Kolumna2
14.50Cztery i pół
2105Sto pięćdziesiąt

Tabela z historią wstawienia rekordu

Dość często zachodzi potrzeba śledzenia daty wstawienia rekordu. Może ona oznaczać czas zaistnienia jakiegoś zdarzenia, czas przyjęcia zamówienia, czas wystawienia faktury. Można uznać, że domyślną wartością tych wszystkich dat będzie czas bieżący serwera.

CREATE TABLE Historia
(
  ID int IDENTITY CONSTRAINT PK_Historia_ID PRIMARY KEY,
  DataUtworzenia datetime CONSTRAINT DF_DataUtworzenia DEFAULT (GETDATE()),
  ZwyklaKolumna varchar(20),
)

INSERT INTO Historia(ZwyklaKolumna) VALUES
('Zwykla kolumna 1'),
('Zwykla kolumna 2')

SELECTFROM Historia

Wykonanie instrukcji SELECT pobierze następujące wyniki:

IDDataUtworzeniaZwyklaKolumna
12013-09-13 19:44:53.107Zwykla kolumna 1
22013-09-13 19:44:53.107Zwykla kolumna 2

Tabela z kolumną oznaczająca stan rekordu

Inną powszechnie wykorzystywaną kolumna jest kolumna stanu. Niepotrzebne rekordy w tabeli można usunąć. Często jednak jest to niemożliwe lub niepożądane. Jeżeli dane rekordu są wykorzystywane przez inne rekordy (są z nimi w relacji), wtedy usunięcie go może doprowadzić do niespójności danych. Można oczywiście usunąć także i te niepójne dane, ale czy zawsze jest to dobre rozwiązanie? W takich przypadkach stosuje się kolumnę stanu. Nowe rekordy mogą wchodzić w relacje tylko z rekordami aktywnych, stare rekordy mogą dalej być w relacji ze starymi rekordami. Popatrzmy na przykładową definicję tabeli:

CREATE TABLE HistoriaZeStatusem
(
  ID int IDENTITY CONSTRAINT PK_HistoriaZeStatusem_ID PRIMARY KEY,
  DataUtworzenia datetime NOT NULL CONSTRAINT DF_DataUtworzenia DEFAULT (GETDATE()),
  Aktywny bit NOT NULL CONSTRAINT DF_Aktywny DEFAULT (1),
  ZwyklaKolumna varchar(20),
)

INSERT INTO HistoriaZeStatusem(ZwyklaKolumna) VALUES
('Zwykla kolumna 1'),
('Zwykla kolumna 2')

SELECTFROM HistoriaZeStatusem

Pobranie danych pozwoli nam uzyskać taki oto rezultat:

IDDataUtworzeniaAktywnyZwyklaKolumna
12013-09-13 19:44:53.1071Zwykla kolumna 1
22013-09-13 19:44:53.1071Zwykla kolumna 2

Aby wstawić rekordy bez wartości domyślnej należy je jawnie wskazać:

INSERT INTO HistoriaZeStatusem(ZwyklaKolumna, Aktywny) VALUES
('Zwykla kolumna 3'DEFAULT),
('Zwykla kolumna 4', 0)

SELECTFROM HistoriaZeStatusem

Słowo kluczowe DEFAULT oznacza, że wstawiona będzie wartość domyślna. Trzecia kolumna dostanie zatem wartość domyślną, a czarta wskazana jawnie wartość 0:

IDDataUtworzeniaAktywnyZwyklaKolumna
12013-09-13 19:44:53.1071Zwykla kolumna 1
22013-09-13 19:44:53.1071Zwykla kolumna 2
12013-09-13 20:33:18.5171Zwykla kolumna 3
22013-09-13 20:33:18.5170Zwykla kolumna 4

Podsumowanie

Przedstawiłem kilka podstawiowych konstrukcji służących do dodawania tabel SQL Server. To, które opcje zostały pokazane wynikało z moich obserwacji i częstotliwości występowania. Gdyby pojawiły się jakieś wątpliwości proszę o ślad w komentarzu. W miarę możliwości będę tutaj dodawał nowe szablony skryptów w celu szybkiego wykonania Kopiuj+Wklej+Zmodyfikuj.

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).
Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?