Microsoft SQL Server 如何实现一个只有一行数据的表

文章目录

        基于计算列的唯一约束
        使用 MERGE 插入和更新
        通过触发器禁止删除数据
        总结

今天我们继续讨论如何在 Microsoft SQL Server 中实现一个只能存储一行数据的表。

    📝如果你使用的是 MySQL，可以通过这篇文章了解如何在 MySQL 中实现一个只有一行数据的表。
    📝如果你使用的是 Oracle，可以通过这篇文章了解如何在 Oracle 中实现一个只有一行数据的表。
    📝如果你使用的是 PostgreSQL，可以通过这篇文章了解如何在 PostgreSQL 中实现一个只有一行数据的表。

假如我们的系统有一个配置表，存储了系统名称、版本等信息，例如：

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



现在要求在第一次部署应用程序时在表中生成一条记录，以后升级应用时更新配置信息，同时需要防止用户误删除该记录。
基于计算列的唯一约束

Microsoft SQL Server 支持计算列（Computed Column），可以基于其他字段计算得到该列的值。当计算列基于确定性的表达式时，可以用于创建索引（类似于函数索引）。因此，我们可以创建一个基于常量的计算列，并且创建一个主键或者唯一约束。例如：

alter table configuration add only_one_row as (1);

alter table configuration add constraint configuration_un unique (only_one_row);



计算列 only_one_row 的值永远等于 1，再加上一个唯一性约束，意味着表中最多只能存储一行数据。
使用 MERGE 插入和更新

在第一次部署应用程序时，可以执行以下 INSERT 语句插入一条数据：

insert into configuration(system_name, system_version, update_date)
values ('ERP 系统', '版本： 2020.01', current_timestamp);

select * from configuration;
system_name|system_version|update_date        |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系统    |版本： 2020.01 |2020-07-23 07:22:54|           1|



如果在升级应用程序时继续插入数据就会返回错误：

insert into configuration(system_name, system_version, update_date)
values ('ERP 系统', '版本： 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).



为了避免唯一约束冲突，我们需要在执行插入语句之前查询一次数据。如果没有结果就插入记录，否则需要更新记录。

update configuration
set system_name = 'ERP 系统',
    system_version = '版本： 2020.02',
    update_date = current_timestamp;

select * from configuration;
system_name|system_version|update_date        |only_one_row|
-----------|--------------|-------------------|------------|
ERP 系统    |版本： 2020.02 |2020-07-23 07:33:12|           1|



一个更简单的方法就是使用 MERGE 语句，它可以同时实现 INSERT、UPDATE 和 DELETE 语句的功能。我们使用 MERGE 语句将上面的操作修改如下：

-- 清除数据
truncate table configuration;

merge into configuration t
using (select 'ERP 系统' 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 系统    |版本： 2020.01 |2020-07-23 07:46:20|           1|



MERGE 语句使用 1=1 作为数据冲突的判断条件，如果表已经存在任何数据则更新，如果不存在则插入数据。
通过触发器禁止删除数据

解决了数据的插入和更新问题，还需要防止用户误删除配置表中的信息。这个需求可以通过触发器来实现，首先创建一个触发器：

create or alter trigger tri_disable_configuration_del
  on configuration
  instead of delete

更多请见：http://www.mark-to-win.com/tutorial/51510.html