如何在SQL Server中创建依赖于默认约束的其他列

问题描述：

我有一个像这样的表

tab1

create table tab1(ID int identity(1,1), Type varchar(10),IsValued bit)

tab1:

ID Type IsValued
----------------
1   S   1
2   R   0
3   R   0
4   S   1
5   S   1
6   R   0
7   S   1

而不是将值插入IsValued列我想在Type ='S'时创建一个约束(不触发)，将IsValued插入为1 并且当Type ='R'时，IsValued应该插入为0

instead of inserting value into IsValued column i want to create one constraint(NOT TRIGGER) when Type ='S' ,IsValued should be inserted as 1 and when Type ='R' ,IsValued should be inserted as 0

like:IsValued = case when Type ='S' then 1 when Type ='R' then 0 end

我如何实现这一目标.

答

您想要一个计算列.例如:

You want a computed column. For example:

CREATE TABLE tab1
(
 ID INT IDENTITY(1,1)
,[Type] VARCHAR(10)
,IsValued AS CASE [Type] WHEN 'S' THEN 1
                         WHEN 'R' THEN 0
             END
)

您可以使用以下语法将其添加到现有表中:

You can add to an existing table using the following syntax:

ALTER TABLE dbo.tab1 ADD IsValued AS CASE [Type] WHEN 'S' THEN 1
                                                 WHEN 'R' THEN 0
                                     END

您可以通过在创建列之后添加关键字PERSISTED来使该列持久化.保留该列意味着该字段存储在磁盘上.当您插入或更新记录时，SQL Server将在此时计算出该值.如果不这样做，则每次访问该行时，SQL Server都必须进行计算.可以在 SQL Server 2005计算列持续存在中找到很好的解释.

You can make the column persisted by adding the keyword PERSISTED after the column creation. Persisting the column means that the field is stored on disk. When you insert or update a record, SQL server will work out the value at that point. If you don't, SQL Server will have to work it out each time you access the row. A good explanation can be found at SQL Server 2005 Computed Column Is Persisted

ALTER TABLE dbo.tab1 ADD IsValued AS CASE [Type] WHEN 'S' THEN 1
                                                 WHEN 'R' THEN 0
                                     END PERSISTED

如何在SQL Server中创建依赖于默认约束的其他列

相关推荐