Microsoft SQL Server 如何實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        基于計(jì)算列的唯一約束
        使用 MERGE 插入和更新
        通過觸發(fā)器禁止刪除數(shù)據(jù)
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們繼續(xù)討論如何在 Microsoft SQL Server 中實(shí)現(xiàn)一個(gè)只能存儲(chǔ)一行數(shù)據(jù)的表。

    ??如果你使用的是 MySQL，可以通過這篇文章了解如何在 MySQL 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。
    ??如果你使用的是 Oracle，可以通過這篇文章了解如何在 Oracle 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。
    ??如果你使用的是 PostgreSQL，可以通過這篇文章了解如何在 PostgreSQL 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。

假如我們的系統(tǒng)有一個(gè)配置表，存儲(chǔ)了系統(tǒng)名稱、版本等信息，例如：

create table configuration(
  system_name varchar(100) not null,
  system_version varchar(50) not null,
  update_date datetime2 not null
);



現(xiàn)在要求在第一次部署應(yīng)用程序時(shí)在表中生成一條記錄，以后升級(jí)應(yīng)用時(shí)更新配置信息，同時(shí)需要防止用戶誤刪除該記錄。
基于計(jì)算列的唯一約束

Microsoft SQL Server 支持計(jì)算列（Computed Column），可以基于其他字段計(jì)算得到該列的值。當(dāng)計(jì)算列基于確定性的表達(dá)式時(shí)，可以用于創(chuàng)建索引（類似于函數(shù)索引）。因此，我們可以創(chuàng)建一個(gè)基于常量的計(jì)算列，并且創(chuàng)建一個(gè)主鍵或者唯一約束。例如：

alter table configuration add only_one_row as (1);

alter table configuration add constraint configuration_un unique (only_one_row);



計(jì)算列 only_one_row 的值永遠(yuǎn)等于 1，再加上一個(gè)唯一性約束，意味著表中最多只能存儲(chǔ)一行數(shù)據(jù)。
使用 MERGE 插入和更新

在第一次部署應(yīng)用程序時(shí)，可以執(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|



如果在升級(jí)應(yīng)用程序時(shí)繼續(xù)插入數(shù)據(jù)就會(huì)返回錯(cuò)誤：

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ù)。如果沒有結(jié)果就插入記錄，否則需要更新記錄。

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|



一個(gè)更簡(jiǎn)單的方法就是使用 MERGE 語句，它可以同時(shí)實(shí)現(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ù)的插入和更新問題，還需要防止用戶誤刪除配置表中的信息。這個(gè)需求可以通過觸發(fā)器來實(shí)現(xiàn)，首先創(chuàng)建一個(gè)觸發(fā)器：

create or alter trigger tri_disable_configuration_del
  on configuration
  instead of delete
as
begin
  raiserror ('禁止刪除版本信息！', 18, 1);  
end



tri_disable_configuration_del 是一個(gè) INSTEAD OF 觸發(fā)器，替代了刪除操作并返回一個(gè)錯(cuò)誤信息。

執(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|



以上刪除語句返回了錯(cuò)誤信息，t_version 中的數(shù)據(jù)仍然存在。

需要注意的是，TRUNCATE TABLE 語句仍然可以清除表中的數(shù)據(jù)，因?yàn)樗粫?huì)觸發(fā) DML 觸發(fā)器。
總結(jié)

為了在 Microsoft SQL Server 中為了實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表，可以創(chuàng)建一個(gè)數(shù)值為常量的計(jì)算列，并且基于該字段創(chuàng)建唯一索引。使用 MERGE 語句實(shí)現(xiàn)插入和更新操作；同時(shí)為了防止數(shù)據(jù)被刪除，可以通過觸發(fā)器禁止刪除操作。

除了本文使用的方法之外，你還有沒有其他的實(shí)現(xiàn)方法？歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??