Блог

Поэтому при создании таблицы я хотел бы добавить условие, гарантирующее, что две строки не полностью идентичны, не принимая идентификатор в счет. Таким образом, один или несколько столбцов могут быть одинаковыми, но не полная строка. Возможно ли это? В идеале я хотел бы, чтобы метод (если он есть) работал в SQLite.

Большое спасибо.

Если все столбцы (кроме первичного ключа) вашей таблицы были определены как NOT NULL тогда UNIQUE ограничение для всех столбцов:

 CREATE TABLE tablename (
    id  INTEGER PRIMARY KEY,
    col1    TEXT NOT NULL,
    col2    TEXT NOT NULL,
    col3    TEXT NOT NULL,
    UNIQUE(col1, col2, col3)
);
  

было бы достаточно, чтобы гарантировать, что все строки уникальны.

Но для такой таблицы, как эта:

 CREATE TABLE tablename (
    id  INTEGER PRIMARY KEY,
    col1    TEXT,
    col2    TEXT,
    col3    TEXT
);
  

ограничение unique не будет перехватывать 2 строки, например:

 id  col1  col2  col3
--------------------
1   a     b     null
2   a     b     null
  

потому null что s не считаются равными.

Вам нужно 2 триггера, один BEFORE INSERT и другой BEFORE UPDATE :

 CREATE TRIGGER unique_row_tablename_insert BEFORE INSERT ON tablename
BEGIN
  SELECT 
    CASE WHEN EXISTS (
      SELECT 1 FROM tablename t
      WHERE t.col1 IS NEW.col1 AND t.col2 IS NEW.col2 AND t.col3 IS NEW.col3 
    ) THEN RAISE (ABORT, 'The row already exists')
    END;    
END;

CREATE TRIGGER unique_row_tablename_update BEFORE UPDATE ON tablename
BEGIN
  SELECT 
    CASE WHEN EXISTS (
      SELECT 1 FROM tablename t
      WHERE t.id <> NEW.id AND t.col1 IS NEW.col1 AND t.col2 IS NEW.col2 AND t.col3 IS NEW.col3
    ) THEN RAISE (ABORT, 'The row already exists')
    END;    
END;
  

Используя оператор IS вместо = вы можете сопоставить null s как равный.
Эти триггеры вызовут ошибку с сообщением: 'The row already exists' и прервут операцию, если новая / обновленная строка соответствует уже существующей строке.

Добавьте уникальный индекс в вашу таблицу для всех столбцов:

 CREATE UNIQUE INDEX idx ON yourTable (col1, col2, ..., colN); -- assuming N columns
  

При наличии этого индекса любая попытка вставить запись, все значения которой соответствуют записи, уже имеющейся в таблице, завершится ошибкой.