SQL Server: jak ograniczyć tabelę, aby zawierała pojedynczy wiersz?

Chcę przechowywać pojedynczy wiersz w tabeli konfiguracji dla mojej aplikacji. Chciałbym wymusić, aby ta tabela zawierała tylko jeden wiersz.

Jaki jest najprostszy sposób na wymuszenie ograniczenia jednego wiersza?

Upewnij się, że jedna z kolumn może zawierać tylko jedną wartość, a następnie ustaw ją jako klucz podstawowy (lub zastosuj ograniczenie niepowtarzalności).

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

Mam wiele takich tabel w różnych bazach danych, głównie do przechowywania konfiguracji. O wiele przyjemniej jest wiedzieć, że jeśli element konfiguracyjny powinien być intem, zawsze będziesz czytać tylko int z DB.

Zwykle stosuję podejście Damiena, które zawsze działało świetnie, ale dodam też jedną rzecz:

CREATE TABLE T1(
    Lock char(1) not null DEFAULT 'X',
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

Dodając „DEFAULT 'X'”, nigdy nie będziesz musiał zajmować się kolumną Lock i nie będziesz musiał pamiętać, która była wartością blokady podczas ładowania tabeli po raz pierwszy.

Możesz przemyśleć tę strategię. W podobnych sytuacjach często uważałem za nieocenione pozostawienie starych rzędów konfiguracji w poszukiwaniu informacji historycznych.

Aby to zrobić, faktycznie masz dodatkową kolumnę creation_date_time(data / godzina wstawienia lub aktualizacji) i wyzwalacz wstawiania lub wstawiania / aktualizacji, który wypełni ją poprawnie bieżącą datą / godziną.

Następnie, aby uzyskać aktualną konfigurację, użyj czegoś takiego:

select * from config_table order by creation_date_time desc fetch first row only

(w zależności od twojego smaku DBMS).

W ten sposób nadal możesz zachować historię do celów odzyskiwania (możesz wprowadzić procedury czyszczenia, jeśli tabela stanie się zbyt duża, ale jest to mało prawdopodobne) i nadal możesz pracować z najnowszą konfiguracją.

Możesz zaimplementować wyzwalacz INSTEAD OF Trigger, aby wymusić ten typ logiki biznesowej w bazie danych.

Wyzwalacz może zawierać logikę, aby sprawdzić, czy rekord już istnieje w tabeli, a jeśli tak, ROLLBACK wstawkę.

Teraz, cofając się, by spojrzeć na szerszą perspektywę, zastanawiam się, czy może istnieje alternatywny i bardziej odpowiedni sposób przechowywania tych informacji, na przykład w pliku konfiguracyjnym lub zmiennej środowiskowej?

Używam pola bitowego dla klucza podstawowego o nazwie IsActive. Więc mogą być maksymalnie 2 wiersze, a sql, aby uzyskać prawidłowy wiersz, to: wybierz * z Ustawień, gdzie IsActive = 1, jeśli tabela ma nazwę Ustawienia.

Oto rozwiązanie, które wymyśliłem dla tabeli typu blokady, która może zawierać tylko jeden wiersz, zawierający Y lub N (na przykład stan blokady aplikacji).

Utwórz tabelę z jedną kolumną. Położyłem ograniczenie sprawdzające na jednej kolumnie, aby można było w niej umieścić tylko Y lub N. (Lub 1 lub 0 lub cokolwiek)

Wstaw jeden wiersz w tabeli ze stanem „normalnym” (np. N oznacza niezablokowane)

Następnie utwórz wyzwalacz INSERT w tabeli, która ma tylko SIGNAL (DB2) lub RAISERROR (SQL Server) lub RAISE_APPLICATION_ERROR (Oracle). To sprawia, że ​​kod aplikacji może aktualizować tabelę, ale każda operacja INSERT kończy się niepowodzeniem.

Przykład DB2:

create table PRICE_LIST_LOCK
(
    LOCKED_YN       char(1)   not null  
        constraint PRICE_LIST_LOCK_YN_CK  check (LOCKED_YN in ('Y', 'N') )
);
--- do this insert when creating the table
insert into PRICE_LIST_LOCK
values ('N');

--- once there is one row in the table, create this trigger
CREATE TRIGGER ONLY_ONE_ROW_IN_PRICE_LIST_LOCK
   NO CASCADE 
   BEFORE INSERT ON PRICE_LIST_LOCK
   FOR EACH ROW
   SIGNAL SQLSTATE '81000'  -- arbitrary user-defined value
     SET MESSAGE_TEXT='Only one row is allowed in this table';

Pracuje dla mnie.

Stare pytanie, ale co powiesz na użycie IDENTITY (MAX, 1) małego typu kolumny?

CREATE TABLE [dbo].[Config](
[ID] [tinyint] IDENTITY(255,1) NOT NULL,
[Config1] [nvarchar](max) NOT NULL,
[Config2] [nvarchar](max) NOT NULL

Możesz napisać wyzwalacz dla akcji wstawiania na stole. Za każdym razem, gdy ktoś próbuje wstawić nowy wiersz do tabeli, odpal logikę usuwania ostatniego wiersza w kodzie wyzwalacza wstawiania.

IF NOT EXISTS ( select * from table )
BEGIN
    ///Your insert statement
END

Tutaj możemy również stworzyć niewidoczną wartość, która będzie taka sama po pierwszym wpisie do bazy danych Przykład: Tabela uczniów: Id: int firstname: char Tutaj w polu wejściowym musimy podać tę samą wartość dla kolumny id, która ograniczy jak po pierwszym wpisie innym niż zapis lock bla bla ze względu na ograniczenie klucza podstawowego, a zatem posiadanie tylko jednego wiersza na zawsze. Mam nadzieję że to pomoże!