Sprawdź ograniczenie tylko jedna z trzech kolumn jest różna od null


61

Mam tabelę (SQL Server), która zawiera 3 typy wyników: FLOAT, NVARCHAR (30) lub DATETIME (3 osobne kolumny). Chcę się upewnić, że dla dowolnego wiersza tylko jedna kolumna ma wynik, a pozostałe kolumny mają wartość NULL. Jakie jest najprostsze ograniczenie sprawdzające, aby to osiągnąć?

Kontekstem tego jest próba ponownego wprowadzenia możliwości przechwytywania wyników nienumerycznych w istniejącym systemie. Dodanie dwóch nowych kolumn do tabeli z ograniczeniem, aby zapobiec więcej niż jednemu wynikowi na wiersz, było najbardziej ekonomicznym podejściem, niekoniecznie poprawnym.

Aktualizacja: Przepraszamy, typ danych snafu. Niestety nie zamierzałem interpretować typów wyników wskazanych jako typy danych SQL Server, tylko ogólne terminy, teraz naprawione.

Odpowiedzi:


72

Następujące czynności powinny załatwić sprawę:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO

24

Prawdopodobnie będziesz musiał wykonać trzy testy w ramach ograniczenia, jeden test dla każdej pary, która ma być pusta, i jeden dla kolumny, która nie powinna mieć wartości zerowej:

ALTER TABLE table
ADD CONSTRAINT CK_one_is_null
CHECK (
     (col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL)
  OR (col2 IS NOT NULL AND col1 IS NULL AND col3 IS NULL) 
  OR (col3 IS NOT NULL AND col1 IS NULL AND col2 IS NULL)
);

To nie jest tak skalowalne, mam tabelę z 9 kluczami obcymi i tylko jeden nie powinien mieć wartości null, wolę rozwiązanie @MarkStoreySmith
Amir Pashazadeh

5

Oto rozwiązanie PostgreSQL wykorzystujące wbudowane funkcje tablicowe :

ALTER TABLE your_table
ADD chk_only_one_is_not_null CHECK (array_length(array_remove(ARRAY[col1::text, col2::text, col3::text], NULL), 1) = 1);

Czy będzie to szybsze wdrożenie w postgreSQL niż wcześniej wspomniane rozwiązania CASE lub AND / OR opublikowane odpowiednio przez Mark Storey i mrdenny?
Chris Britt
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.