個人中心
退出
Microsoft SQL Server 如何實現(xiàn)一個只有一行數(shù)據(jù)的表
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
基于計算列的唯一約束
使用 MERGE 插入和更新
通過觸發(fā)器禁止刪除數(shù)據(jù)
總結
大家好，我是只談技術不剪發(fā)的 Tony 老師。今天我們繼續(xù)討論如何在 Microsoft SQL Server 中實現(xiàn)一個只能存儲一行數(shù)據(jù)的表。
??如果你使用的是 MySQL，可以通過這篇文章了解如何在 MySQL 中實現(xiàn)一個只有一行數(shù)據(jù)的表。
??如果你使用的是 Oracle，可以通過這篇文章了解如何在 Oracle 中實現(xiàn)一個只有一行數(shù)據(jù)的表。
??如果你使用的是 PostgreSQL，可以通過這篇文章了解如何在 PostgreSQL 中實現(xiàn)一個只有一行數(shù)據(jù)的表。
假如我們的系統(tǒng)有一個配置表，存儲了系統(tǒng)名稱、版本等信息，例如：
create table configuration(
system_name varchar(100) not null,
system_version varchar(50) not null,
update_date datetime2 not null
);
現(xiàn)在要求在第一次部署應用程序時在表中生成一條記錄，以后升級應用時更新配置信息，同時需要防止用戶誤刪除該記錄。
基于計算列的唯一約束
Microsoft SQL Server 支持計算列（Computed Column），可以基于其他字段計算得到該列的值。當計算列基于確定性的表達式時，可以用于創(chuàng)建索引（類似于函數(shù)索引）。因此，我們可以創(chuàng)建一個基于常量的計算列，并且創(chuàng)建一個主鍵或者唯一約束。例如：
alter table configuration add only_one_row as (1);
alter table configuration add constraint configuration_un unique (only_one_row);
計算列 only_one_row 的值永遠等于 1，再加上一個唯一性約束，意味著表中最多只能存儲一行數(shù)據(jù)。
使用 MERGE 插入和更新
在第一次部署應用程序時，可以執(zhí)行以下 INSERT 語句插入一條數(shù)據(jù)：
insert into configuration(system_name, system_version, update_date)
values ('ERP 系統(tǒng)', '版本： 2020.01', current_timestamp);
select * from configuration;
system_name|system_version|update_date |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系統(tǒng) |版本： 2020.01 |2020-07-23 07:22:54| 1|
如果在升級應用程序時繼續(xù)插入數(shù)據(jù)就會返回錯誤：
insert into configuration(system_name, system_version, update_date)
values ('ERP 系統(tǒng)', '版本： 2020.02', current_timestamp);
SQL Error [2627] [23000]: Violation of UNIQUE KEY constraint 'configuration_un'. Cannot insert duplicate key in object 'dbo.configuration'. The duplicate key value is (1).
為了避免唯一約束沖突，我們需要在執(zhí)行插入語句之前查詢一次數(shù)據(jù)。如果沒有結果就插入記錄，否則需要更新記錄。
update configuration
set system_name = 'ERP 系統(tǒng)',
system_version = '版本： 2020.02',
update_date = current_timestamp;
select * from configuration;
system_name|system_version|update_date |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系統(tǒng) |版本： 2020.02 |2020-07-23 07:33:12| 1|
一個更簡單的方法就是使用 MERGE 語句，它可以同時實現(xiàn) INSERT、UPDATE 和 DELETE 語句的功能。我們使用 MERGE 語句將上面的操作修改如下：
-- 清除數(shù)據(jù)
truncate table configuration;
merge into configuration t
using (select 'ERP 系統(tǒng)' system_name, '版本： 2020.01' system_version, current_timestamp update_date) s
on (1=1)
when matched then
update set t.system_name = s.system_name,
t.system_version = s.system_version,
t.update_date = s.update_date
when not matched then
insert (system_name, system_version, update_date)
values (s.system_name, s.system_version, s.update_date);
system_name|system_version|update_date |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系統(tǒng) |版本： 2020.01 |2020-07-23 07:46:20| 1|
MERGE 語句使用 1=1 作為數(shù)據(jù)沖突的判斷條件，如果表已經(jīng)存在任何數(shù)據(jù)則更新，如果不存在則插入數(shù)據(jù)。
通過觸發(fā)器禁止刪除數(shù)據(jù)
解決了數(shù)據(jù)的插入和更新問題，還需要防止用戶誤刪除配置表中的信息。這個需求可以通過觸發(fā)器來實現(xiàn)，首先創(chuàng)建一個觸發(fā)器：
create or alter trigger tri_disable_configuration_del
on configuration
instead of delete
as
begin
raiserror ('禁止刪除版本信息！', 18, 1);
end
tri_disable_configuration_del 是一個 INSTEAD OF 觸發(fā)器，替代了刪除操作并返回一個錯誤信息。
執(zhí)行以下語句刪除配置數(shù)據(jù)：
delete from configuration
where only_one_row = 1;
SQL Error [50000] [S0001]: 禁止刪除版本信息！
system_name|system_version|update_date |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系統(tǒng) |版本： 2020.02 |2020-07-23 07:51:10| 1|
以上刪除語句返回了錯誤信息，t_version 中的數(shù)據(jù)仍然存在。
需要注意的是，TRUNCATE TABLE 語句仍然可以清除表中的數(shù)據(jù)，因為它不會觸發(fā) DML 觸發(fā)器。
總結
為了在 Microsoft SQL Server 中為了實現(xiàn)一個只有一行數(shù)據(jù)的表，可以創(chuàng)建一個數(shù)值為常量的計算列，并且基于該字段創(chuàng)建唯一索引。使用 MERGE 語句實現(xiàn)插入和更新操作；同時為了防止數(shù)據(jù)被刪除，可以通過觸發(fā)器禁止刪除操作。
除了本文使用的方法之外，你還有沒有其他的實現(xiàn)方法？歡迎關注??、評論??、點贊??
分類導航