blocks|key|900448|text|确保其中一列只能包含一个值，然后将其设置为主键(或应用唯一性约束)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900449|CREATE+TABLE+T1(
++++Lock+char(1)+not+null,
++++/*+Other+columns+*/,
++++constraint+PK_T1+PRIMARY+KEY+(Lock),
++++constraint+CK_T1_Locked+CHECK+(Lock='X')
)|code-block|syntax|javascript|900450|我在不同的数据库中有许多这样的表，主要用于存储配置。知道如果配置项应该是一个int，那么您将只能从DB中读取int，这要好得多。|900451|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint).

<pre><code>CREATE TABLE T1(
 Lock char(1) not null,
 /* Other columns */,
 constraint PK_T1 PRIMARY KEY (Lock),
 constraint CK_T1_Locked CHECK (Lock='X')
)
</code></pre>

I have a number of these tables in various databases, mostly for storing config. It's a lot nicer knowing that, if the config item should be an int, you'll only ever read an int from the DB.

blocks|key|900512|text|我通常使用达米恩的方法，这对我来说一直很有效，但我还补充了一件事：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900513|CREATE+TABLE+T1(
++++Lock+char(1)+not+null+DEFAULT+'X',
++++/*+Other+columns+*/,
++++constraint+PK_T1+PRIMARY+KEY+(Lock),
++++constraint+CK_T1_Locked+CHECK+(Lock='X')
)|code-block|syntax|javascript|900514|添加“默认的'X'"，您将永远不需要处理Lock列，也不需要在第一次装入表时记住哪个是锁值。|900515|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I usually use Damien's approach, which has always worked great for me, but I also add one thing:

<pre><code>CREATE TABLE T1(
 Lock char(1) not null DEFAULT 'X',
 /* Other columns */,
 constraint PK_T1 PRIMARY KEY (Lock),
 constraint CK_T1_Locked CHECK (Lock='X')
)
</code></pre>

Adding the "DEFAULT 'X'", you will never have to deal with the Lock column, and won't have to remember which was the lock value when loading the table for the first time.

blocks|key|900349|text|你可能想重新考虑这个策略。在类似的情况下，我经常发现留下旧的配置行作为历史信息是非常有价值的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900350|为此，您实际上有一个额外的列creation_date_time+(插入或更新的日期/时间)和一个insert或insert/update触发器，它将用当前日期/时间正确地填充它。|offset|length|style|CODE|900351|然后，为了获得当前的配置，您可以使用如下命令：|900352|select+*+from+config_table+order+by+creation_date_time+desc+fetch+first+row+only|code-block|syntax|javascript|900353|(取决于您的DBMS风格)。|900354|这样，您仍然可以出于恢复目的维护历史记录(如果表太大，您可以设置清理过程，但这不太可能)，并且您仍然可以使用最新的配置。|900355|entityMap^0|0|E|I|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

You may want to rethink this strategy. In similar situations, I've often found it invaluable to leave the old configuration rows lying around for historical information.

To do that, you actually have an extra column <code>creation_date_time</code> (date/time of insertion or update) and an insert or insert/update trigger which will populate it correctly with the current date/time.

Then, in order to get your current configuration, you use something like:

<pre><code>select * from config_table order by creation_date_time desc fetch first row only
</code></pre>

(depending on your DBMS flavour).

That way, you still get to maintain the history for recovery purposes (you can institute cleanup procedures if the table gets too big but this is unlikely) and you still get to work with the latest configuration.

blocks|key|1115007|text|您可以实现而不是Trigger，以在数据库中强制执行这种类型的业务逻辑。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1115008|触发器可以包含用于检查表中是否已存在记录的逻辑，如果存在，则回滚插入。|1115009|现在，退一步看一下更大的图景，我想知道是否有其他更合适的替代方法来存储此信息，例如，在配置文件或环境变量中？|1115010|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189799.aspx^0|8|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

You can implement an INSTEAD OF <a href="http://msdn.microsoft.com/en-us/library/ms189799.aspx" rel="nofollow">Trigger</a> to enforce this type of business logic within the database.

The trigger can contain logic to check if a record already exists in the table and if so, ROLLBACK the Insert.

Now, taking a step back to look at the bigger picture, I wonder if perhaps there is an alternative and more suitable way for you to store this information, perhaps in a configuration file or environment variable for example?

blocks|key|900800|text|我知道这很古老，但与其考虑大问题，有时不如考虑小问题，使用如下形式的恒等整数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900801|Create+Table+TableWhatever
(
++++keycol+int+primary+key+not+null+identity(1,1)+
+++++++++check(keycol+=1),
++++Col2+varchar(7)
)|code-block|syntax|javascript|900802|这样，每次您尝试插入另一行时，check约束将引发，阻止您插入任何行，因为标识p键不接受除1以外的任何值|900803|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I know this is very old but instead of thinking BIG sometimes better think small use an identity integer like this:
<pre><code>Create Table TableWhatever
(
 keycol int primary key not null identity(1,1) 
 check(keycol =1),
 Col2 varchar(7)
)
</code></pre>
This way each time you try to insert another row the check constraint will raise preventing you from inserting any row since the identity p key won't accept any value but 1

blocks|key|1115473|text|这是我为一个锁类型的表想出的一个解决方案，它只能包含一行，其中包含Y或N(例如，应用程序锁状态)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1115474|创建包含一列的表。我在一列上放置了一个检查约束，以便只能在其中放入Y或N。(或1或0，或其他任何值)|1115475|在表中插入一行，状态为“正常”(例如，N表示未锁定)|1115476|然后在只有一个信号(DB2)、RAISERROR+(SQL+Server)或RAISE_APPLICATION_ERROR+(Oracle)的表上创建INSERT触发器。这使得应用程序代码可以更新表，但任何插入都会失败。|1115477|DB2示例：|1115478|create+table+PRICE_LIST_LOCK
(
++++LOCKED_YN+++++++char(1)+++not+null++
++++++++constraint+PRICE_LIST_LOCK_YN_CK++check+(LOCKED_YN+in+('Y',+'N')+)
);
---+do+this+insert+when+creating+the+table
insert+into+PRICE_LIST_LOCK
values+('N');

---+once+there+is+one+row+in+the+table,+create+this+trigger
CREATE+TRIGGER+ONLY_ONE_ROW_IN_PRICE_LIST_LOCK
+++NO+CASCADE+
+++BEFORE+INSERT+ON+PRICE_LIST_LOCK
+++FOR+EACH+ROW
+++SIGNAL+SQLSTATE+'81000'++--+arbitrary+user-defined+value
+++++SET+MESSAGE_TEXT='Only+one+row+is+allowed+in+this+table';|code-block|syntax|javascript|1115479|这对我很有效。|1115480|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|X|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Here's a solution I came up with for a lock-type table which can contain only one row, holding a Y or N (an application lock state, for example).

Create the table with one column. I put a check constraint on the one column so that only a Y or N can be put in it. (Or 1 or 0, or whatever)

Insert one row in the table, with the "normal" state (e.g. N means not locked)

Then create an INSERT trigger on the table that only has a SIGNAL (DB2) or RAISERROR (SQL Server) or RAISE_APPLICATION_ERROR (Oracle). This makes it so application code can update the table, but any INSERT fails.

DB2 example:

<pre><code>create table PRICE_LIST_LOCK
(
 LOCKED_YN char(1) not null 
 constraint PRICE_LIST_LOCK_YN_CK check (LOCKED_YN in ('Y', 'N') )
);
--- do this insert when creating the table
insert into PRICE_LIST_LOCK
values ('N');

--- once there is one row in the table, create this trigger
CREATE TRIGGER ONLY_ONE_ROW_IN_PRICE_LIST_LOCK
 NO CASCADE 
 BEFORE INSERT ON PRICE_LIST_LOCK
 FOR EACH ROW
 SIGNAL SQLSTATE '81000' -- arbitrary user-defined value
 SET MESSAGE_TEXT='Only one row is allowed in this table';
</code></pre>

Works for me.

blocks|key|1115438|text|我使用名为IsActive的主键的位字段。因此，最多可以有2行，用于获取有效行的sql是:如果表名为Settings，则选择*+from+Settings+where+IsActive+=1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1115439|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I use a bit field for primary key with name IsActive.
So there can be 2 rows at most and and the sql to get the valid row is:
select * from Settings where IsActive = 1
if the table is named Settings.

blocks|key|1115517|text|老问题，但是如何使用小列类型的标识(MAX，1)呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1115518|CREATE+TABLE+[dbo].[Config](
[ID]+[tinyint]+IDENTITY(255,1)+NOT+NULL,
[Config1]+[nvarchar](max)+NOT+NULL,
[Config2]+[nvarchar](max)+NOT+NULL|code-block|syntax|javascript|1115519|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Old question but how about using IDENTITY(MAX,1) of a small column type?

<pre><code>CREATE TABLE [dbo].[Config](
[ID] [tinyint] IDENTITY(255,1) NOT NULL,
[Config1] [nvarchar](max) NOT NULL,
[Config2] [nvarchar](max) NOT NULL
</code></pre>

blocks|key|900294|text|您可以在表的insert操作上编写触发器。每当有人试图在表中插入新行时，就删除insert触发器代码中最新行的逻辑。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900295|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can write a trigger on the insert action on the table. Whenever someone tries to insert a new row in the table, fire away the logic of removing the latest row in the insert trigger code.

blocks|key|900262|text|IF+NOT+EXISTS+(+select+*+from+table+)
BEGIN
++++///Your+insert+statement
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|900263|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>IF NOT EXISTS ( select * from table )
BEGIN
 ///Your insert statement
END
</code></pre>

blocks|key|1115377|text|在这里我们还可以设置一个不可见的值，这个值在第一次进入database.Example:+Student+Table:+Id:int+firstname:char之后是相同的。在这里，在输入框中，我们必须为id列指定相同的值，该列将限制为第一次输入之后的值，而不是由于主键约束而写入lock+bla+bla，从而永远只有一行。希望这能有所帮助！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1115378|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Here we can also make an invisible value which will be the same after first entry in the database.Example:
Student Table:
Id:int
firstname:char
Here in the entry box,we have to specify the same value for id column which will restrict as after first entry other than writing lock bla bla due to primary key constraint thus having only one row forever.
Hope this helps!

I want to store a single row in a configuration table for my application. I would like to enforce that this table can contain only one row. 

What is the simplest way to enforce the single row constraint ?

SQL Server: how to constrain a table to contain a single row?

SQL Server

数据库

我想在我的应用程序的配置表中存储一行。我想强制这个表只能包含一行。实施单行约束的最简单方法是什么？

问SQL Server:如何将表约束为只包含一行？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:如何将表约束为只包含一行？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:如何将表约束为只包含一行？
EN