Użyj funkcji „LEN” w klauzuli „WHERE” w „CREATE UNIQUE INDEX”

Mam ten stół:

CREATE TABLE Table01 (column01 nvarchar(100));

I chcę utworzyć unikalny indeks na kolumnie 01 z tym warunkiem LEN (kolumna01)> = 5

Próbowałem:

CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE LEN(column01) >= 5;

Mam:

Niepoprawna klauzula WHERE dla filtrowanego indeksu „UIX_01” w tabeli „Table01”.

I :

ALTER TABLE Table01 ADD column01_length AS (LEN(column01));
CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE column01_length >= 5;

Produkuje:

Filtrowanego indeksu „UIX_01” nie można utworzyć w tabeli „Table01”, ponieważ kolumna „column01_length” w wyrażeniu filtru jest kolumną obliczoną. Przepisz wyrażenie filtru, aby nie zawierało tej kolumny.

Jedną z metod obejścia filtrowanego ograniczenia indeksu jest widok indeksowany:

CREATE TABLE dbo.Table01 (
  Column01 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01);
GO

INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --duplicate key error
GO

EDYTOWAĆ:

Jak zdefiniować widok, jeśli mam dwie kolumny w indeksie? UTWÓRZ UNIKALNY INDEKS UIX_01 NA Table01 (column01, column02) GDZIE DŁUGOŚĆ (column01)> = 5

Podejście do widoku indeksowanego można rozszerzyć dla klucza złożonego, dodając inne kolumny kluczy do definicji widoku i indeksu. Ten sam filtr jest stosowany w definicji widoku, ale unikalność kwalifikujących się wierszy jest wymuszana przez klucz złożony zamiast wartości pojedynczej kolumny:

CREATE TABLE dbo.Table01 (
   Column01 NVARCHAR(100)
  ,Column02 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01, Column02
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01, Column02)
GO

INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --duplicate key error
GO

To wydaje się być kolejnym z wielu ograniczeń filtrowanych indeksów. Próba obejścia go przy LIKEużyciu WHERE column01 LIKE '_____'również nie działa, co powoduje wyświetlenie tego samego komunikatu o błędzie ( „Niepoprawna klauzula WHERE ...” ).

Oprócz VIEWrozwiązania innym sposobem byłoby przekonwertowanie kolumny obliczanej na zwykłą i dodanie CHECKograniczenia, tak aby zawsze zawierało prawidłowe dane:

CREATE TABLE Table01 (column01 nvarchar(100),
                      column01_length int,
                      CHECK ( column01_length = len(column01)
                              AND column01 IS NOT NULL 
                              AND column01_length IS NOT NULL
                           OR column01 IS NULL 
                              AND column01_length IS NULL )
                     ) ;


CREATE UNIQUE INDEX UIX_01 ON Table01 (column01) WHERE column01_length >= 5 ;

Testowany na rextester.com

Oczywiście oznacza to, że musisz jawnie wypełnić column01_lengthodpowiednią długość przy każdym wypełnianiu column01(w przypadku wstawek i aktualizacji). Może to być trudne, ponieważ musisz upewnić się, że długość jest obliczana w taki sam sposób, jak LEN()robi to funkcja T-SQL . W szczególności należy pominąć końcowe spacje, co niekoniecznie jest domyślnym sposobem obliczania długości w różnych językach programowania, w których zapisywane są aplikacje klienckie. Logika może być łatwa do uwzględnienia w programie wywołującym, ale musisz być świadomy różnicy w pierwszej kolejności.

Opcją może być INSERT/UPDATEwyzwalacz ^1, który poda poprawną wartość dla kolumny, więc wygląda na obliczoną dla aplikacji klienckich.

_{¹ Jak wyjaśniono w Wyzwalacze w porównaniu do ograniczeń , w tym celu należy użyć wyzwalacza INSTEAD OF. Wyzwalacz PO, po prostu nigdy się nie wykona, ponieważ nieobecna długość nie spełni ograniczenia sprawdzania, a to z kolei uniemożliwi uruchomienie wyzwalacza. INSTEAD OF wyzwalaczy ma jednak swoje własne ograniczenia (zobacz Krótki przewodnik dotyczący planowania wyzwalaczy DML ).}

Nie jestem pewien, jak to się sprawdzi i może istnieć o wiele łatwiejszy sposób na osiągnięcie tego, co przeoczyłem, ale powinno to zrobić wszystko, czego potrzebujesz, jeśli chcesz egzekwować wyjątkowość.

CREATE TABLE dbo.Table01 
(
  Column01 NVARCHAR(100)
);
GO

CREATE FUNCTION dbo.ChkUniqueColumn01OverLen5()
RETURNS BIT
AS
BEGIN
DECLARE @Result BIT, @Count BIGINT, @DistinctCount BIGINT

SELECT  @Count = COUNT(Column01),
        @DistinctCount = COUNT(DISTINCT Column01)
FROM    Table01
WHERE   LEN(Column01) >= 5 

SELECT @Result = CASE WHEN @Count = @DistinctCount THEN 1 ELSE 0 END

RETURN @Result

END;
GO

ALTER TABLE dbo.Table01
ADD CONSTRAINT Chk_UniqueColumn01OverLen5
CHECK (dbo.ChkUniqueColumn01OverLen5() = 1);
GO

INSERT dbo.Table01 (Column01)
VALUES (N'123'), (N'1234');
GO

INSERT dbo.Table01 (Column01)
VALUES (N'12345');
GO

INSERT dbo.Table01 (Column01)
VALUES (N'12345'); -- Will fail
GO

INSERT dbo.Table01 (Column01)
VALUES (N'123'); -- Will pass
GO

UPDATE dbo.Table01
SET Column01 = '12345'
WHERE Column01 = '1234' -- Will fail
GO

SELECT * FROM dbo.Table01;
GO

DROP TABLE Table01;
DROP FUNCTION dbo.ChkUniqueColumn01OverLen5;