Как правильно удалить запись из базы данных?

Question

[Григорий]

Доброго времени суток.
Есть некая таблица:
`id`, `name`, `deleted`, `date_create`, `date_delete`
name - уникальный ключ.
При удалении объекта, запись не удаляется физически а помечается как удаленная. (`deleted`=1, `date_delete`=NOW())
Если попытаться вставить новую запись с именем(`name`) которая была удалена - получим duplicate key ... и здесь все логично.

Интересует подход к данной ситуации в принципе.

У меня родилось два с половиной решения данной задачи:
1. Если есть удаленная запись с таким именем то ресторим ее. Но в этом случае, если к данной записи есть привязки, то мы создаем непонятную ситуацию для юзера т.к. он получит объект с кучей привязок, который ранее создавал не он. Не очень красиво.
2. Создать дополнительное поле а-ля `hash` и сделать составным уникальным ключом [`name`,`hash`]. Поле `hash` по умолчанию заполнять чем то вроде "_", а при удалении устанавливать уникальный хэш. (`hash`=MD5(NOW().....), `deleted`=1, `date_delete`=NOW()).
2.5 Почти тоже самое что и в п.2 только без дополнительного поля. Просто при удалении конкатенировать имя с уникальным хэшем.

Пока что вариант №2 кажется мне самым просты и понятным, но есть стойкое ощущение что я чего то не знаю.

Подскажите пожалуйста как удаляете записи Вы?

Answer 1

[Константин Цветков]

Есть некая таблица:
`id`, `name`, `deleted`, `date_create`, `date_delete`
name - уникальный ключ.

Убрать уникальность, так как новая запись с одинаковым значением будет новая. Если вам нужно различать записи, добавляйте к NAME "(уд. 13.03.18)".

Расширенный ответ

CREATE TABLE [dbo].[CertainTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[DateCreated] [datetime] NOT NULL,
	[DateDeleted] [datetime] NULL,
	[NamePower]  AS (((([Name]+' (')+CONVERT([varchar],[DateCreated],(4)))+isnull('~'+CONVERT([varchar],[DateDeleted],(4)),''))+')'),
	[IsDeleted]  AS (CONVERT([bit],[DateDeleted])),
 CONSTRAINT [PK_CertainTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CertainTable] ADD  CONSTRAINT [DF_CertainTable_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO

Наличие зависимых полей deleted и date_delete — нарушение нормальной формы. Второе оставляем, первое вычисляем. Так же вычисляем наименование с датами.
Результат:

1 TEST 2018-03-11 00:00:00.000 2018-03-12 00:00:00.000 TEST (11.03.18~12.03.18) True
2 TEST 2018-03-13 08:31:19.143 NULL                          TEST (13.03.18)          NULL

Comments

[Григорий]

Понятно. Спасибо большое за ответ!

Answer 2

[Петр]

Если хочется делать с `hash`, то:
1. Делайте UNIQUE index только на поле `hash`
2. Генерируйте `hash` только от Name (MD5(Name)), когда у вас поле не удалено. А помечая удаленным, меняйте и `hash` на другой алгоритм (для примера: MD5(Name + Date of delete)). В таком случае останется уникальность по Name для не удаленных, а удаленные не будут мешать. переименовывать значимые поля - плохо.

PS: Если имя у вас уникально, то зачем вам поле ID?

Comments

[Григорий]

Я Вас понял, спасибо за ответ!

Интовый ID приятнее использовать в качестве `foreign key`, по крайней мере мне.
Например `name`ы в одной из таблиц могут быть такого вида:
Cisco CIVS-IPC-6000P
Hikvision DS-2CD8153F-E v1
Hikvision DS-2CD8153F-E v2
и т.д.
Делать ссылки по текстовому полю было бы куда сложнее, даже при наличии каскада.
Плюс `name` редактируемое поле, в этом случае нужно было бы передавать old_name на бэкенд при редактировании из консоли чтобы обновить объект.
Ну и в API проще передать и завалидировать число нежели "текст" как минимум при методах get, delete, copy.

Answer 3

[ThunderCat]

1) Снять уникальность с поля, если есть возможность добавить ТАКОЕ ЖЕ имя под другим id то это нарушает логику работы. Если есть необходимость соблюсти активное состояние уникальным - проверяйте программно перед добавлением новой записи.
2) Это вполне нормальная практика, частая задача, например для записи логинов. Уникальность проще проверить перед вставкой чем искать причины ошибок при дублировании ключей.

Answer 4

[cicatrix]

Самой малой кровью: уберите уникальность с name и добавьте уникальность на комбинацию name, deleted, date_delete
(Уникальные ключи можно добавлять сразу на несколько полей).

ALTER TABLE dbo.имятаблицы
  ADD CONSTRAINT uq_имятаблицы UNIQUE(name, deleted, date_delete)

P.S. Не совсем идеально, конечно, то есть надо будет следить, что у неудалённых записей date_delete всегда была бы NULL, но в целом, это позволит существовать только одной неудалённой записи с тем или иным name, но любому количеству удалённых записей с этим же name (лишь бы date_deleted были разными)

Answer 5

[d-stream]

Ноги у флага deleted растут из древних-древних времен, когда "базой данных" обзывали разрозненное скопище файлов почти регулярной структуры, а хранились эти файлы на дискетах и каждое действие с этой "базой данных" стоило ресурсов и временных и механических в виде износа головок/дискеты )

На сегодняшний день ядра распространенных СУБД позволяют выстраивать полноценные схемы БД, указывая связи между таблицами (constrains, references, cascades и т.п.)
И в общем-то принципиальных противопоказаний для реального удаления, а не пометки записей - нет.
При желании/хотении истории операций - можно сливать удаленные записи в отдельную таблицу истории (возможно трансформировав и порезав не имеющие актуальности данные).