SQL数据库单例，用于存储db版本信息(SQL Database singleton for storing db version information)

SQL数据库单例，用于存储db版本信息(SQL Database singleton for storing db version information)

我在SQL数据库中需要一个非常简单的东西 - 我使用的是SQL Server和/或SQL Compact。 在c＃中，我会这样写

public class MyApp { public static int Version = 1; }

例如，我需要在SQL数据库中以单例形式存储配置信息。 有没有比创建只有一条记录的表更好的方法？ 实际上，对于我目前的需求，只有一个版本号与数据库一起存储就足够了，但它必须同时适用于SQL Server和SQL Compact数据库。

I need a very simple thing in SQL Database - I am using SQL Server and/or SQL Compact. In c# I would write it like this

public class MyApp { public static int Version = 1; }

e.g. I need to store configuration information in a form of singleton in SQL database. Is there any better method than to create table with only one record? Actually for my present needs it would be sufficient to have only one version number stored with database, but it must work both for SQL Server and SQL Compact database.

最满意答案

一行表可能是您最好的方法。 通常，您使用CHECK（）约束来保证您只有一行。

create table your_table_name ( one_row integer not null unique default 1 check (one_row = 1), version varchar(5) not null unique ); insert into your_table_name values (1, '0.0.0');

如果您的平台不支持CHECK（）约束，但支持GRANT和REVOKE，则可以在版本表中插入一行，然后仅授予更新权限。 （撤消删除和插入权限。）

如果您的平台不支持CHECK（）约束，并且不支持GRANT和REVOKE，但支持外键引用，则可以使用对单行表的外键引用替换上面的CHECK（）约束。 这并没有完全解决问题 - 你仍然有一个单行表，你不能充分约束。

如果dbms支持CHECK（）约束中的正则表达式，则可以添加其他约束以保证版本号遵循正则表达式。 您还可以将“版本”拆分为多个整数列，每个列都有自己的约束。 但varchar（5）和varchar（7）似乎是最常见的。

A table with one row is probably your best approach. Normally, you'd use a CHECK() constraint to guarantee you'll have only one row.

create table your_table_name ( one_row integer not null unique default 1 check (one_row = 1), version varchar(5) not null unique ); insert into your_table_name values (1, '0.0.0');

If your platform doesn't support CHECK() constraints, but does support GRANT and REVOKE, you might be able to insert one row into the version table, then grant only update permissions. (Revoke delete and insert permissions.)

If your platform doesn't support CHECK() constraints, and doesn't support GRANT and REVOKE, but does support foreign key references, you might be able to replace the CHECK() constraint above with a foreign key reference to single-row table. This doesn't entirely solve the problem--you still have a single-row table that you can't adequately constrain.

If your dbms supports regular expressions in CHECK() constraints, you could add additional constraints to guarantee your version number follows a regular expression. You could also split the "version" into several columns of integers, each with its own constraints. But varchar(5) and varchar(7) seem to be the most common.