czwartek, 15 września 2011

Intregralność danych i wartości NULL

Pracowałem nad projektem, gdzie musiałem zapewnić integralność danych w tabeli, której kolumny mogą przyjmować wartość NULL. Do tego wartości w jednej kolumnie były zależne od danch w innej kolumnie. Do rozwiązania były następujące problemy:
  • Wartości w kolumnach x i y muszą unikatowe z założeniem, że x i y mogą wielokrotnie przyjmować wartość NULL
  • Jeżeli wartość w kolumnie x jest NULL to wartość w kolumnie y także musi być NULL i na odwrót, y może mieć wartość NULL tylko w przypadku gdy x jest NULL
Poniższy fragment kodu posży nam do utworzenia przykładowych tabel.

IF OBJECT_ID('SOURCES') IS NOT NULL
DROP TABLE SOURCES
GO

CREATE TABLE SOURCES (
SOURCE_ID INT NOT NULL primary key,
SOURCE_NAME NVARCHAR(100) NOT NULL
)
GO

INSERT INTO SOURCES
SELECT '1', 'Source 1' UNION
SELECT '2', 'Source 2'
GO

IF OBJECT_ID('ARTICLES') IS NOT NULL
DROP TABLE ARTICLES
GO

CREATE TABLE ARTICLES (
ID INT IDENTITY NOT NULL PRIMARY KEY,
TITLE NVARCHAR(100) NOT NULL,
FOREIGN_ID NVARCHAR(10) NULL,
SOURCE_ID INT NULL,
CONSTRAINT FK_AS FOREIGN KEY(SOURCE_ID) REFERENCES SOURCES(SOURCE_ID)
)


Postawione wcześniej warunki odnoszą się do kolumn FOREIGN_ID oraz SOURCE_ID w tabeli ARTICLES.

Pierwszym orzechem do zgryzienia był problem zapewnienia by FOREIGN_ID oraz SOURCE_ID były unikatowe, jednakże dopuszczalna jest sytuacja, gdy artykuł nie posiada żadnego źródła. Na początku pomyślałem, że UNIQUE Cosntraint rozwiąże problem, ale od razu zapaliła mi się w głowie lampka. Te rozwiązanie zadziałałoby tylko dla pierwszego rekordu z podaną kombinacją danych a to nie było to czego potrzebowałem. Tak więc skorzystać musiałem z innego rozwiązania.

Unikatowy indeks na kolumnach zawierających wartoci NULL

SQL Server 2008 umożliwia zakładanie indeksów zawierających filter. Filtrem w tym przypadku była wartość NULL w obu kolumnach.

CREATE UNIQUE INDEX IDX_UNIQUE ON ARTICLES(FOREIGN_ID, SOURCE_ID)
WHERE FOREIGN_ID IS NOT NULL AND SOURCE_ID IS NOT NULL

Tak zadeklarowany indeks sprawi, że będzie on składał się tylko z tych rekordów, które przyjmują wartości w obu kolumnach.

Poniższe zapytania przetestują utworzony indeks

insert into articles (title)
select 'Article 1' union
select 'Article 2'


insert into articles (title, foreign_id, source_id)
select 'Article 3', '1', 1 union
select 'Article 4', '1', 1


Pierwszy INSERT wykona się bez błędów, natomiast drugi zwróci następujący błąd

Cannot insert duplicate key row in object 'dbo.ARTICLES' with unique index 'IDX_UNIQUE'.

Tak więc pierwszy problem został rozwiązany. Pozostaje jeszcze drugi aby rekordy nie zawierały źródła i klucza obcego, lub też by zawierały obie wartości jednocześnie.

Wartość NULL w kolumnie z warunkiem

Tutaj z pomocą przychodzi CHECK constraint. Umożliwia on dodanie danych, tylko w przypadku spełnienia jakiegoś warunku.

W tym przypadku warunkiem jest, że albo obie kolumny przyjmują wartość NULL albo żadna z nich.

ALTER TABLE ARTICLES ADD CHECK ((FOREIGN_ID IS NULL AND SOURCE_ID IS NULL) OR (FOREIGN_ID IS NOT NULL AND SOURCE_ID IS NOT NULL))

Powyższy fragment kodu spełni postawiony wcześniej warunek.

Z kolei poniższe 3 zapytania przetestują, czy wszystko działa tak jak powinno

insert into articles (title, foreign_id, source_id) values ('Article 3', NULL, 1)

insert into articles (title, foreign_id, source_id) values ('Article 3', '1', NULL)

insert into articles (title, foreign_id, source_id) values ('Article 3', '1', 1)

Pierwsze dwa zapytania zwrócą nastepujący błąd. Jedna z kolumn przyjmuje wartość NULL

The INSERT statement conflicted with the CHECK constraint "CK__ARTICLES__1D7B6025". The conflict occurred in database "tempdb", table "dbo.ARTICLES"

Natomiast ostatnie zapytanie pomyślnie zostanie wykonane.

Na koniec jeszcze krótki fragment kodu, który usunie utworzone wcześniej tabele.

IF OBJECT_ID('ARTICLES') IS NOT NULL
DROP TABLE ARTICLES
GO

IF OBJECT_ID('SOURCES') IS NOT NULL
DROP TABLE SOURCES
GO

Prześlij komentarz