如何使视图列 NOT NULL [英] How to make a view column NOT NULL

问题描述

我正在尝试创建一个视图，在该视图中我希望一列只有 true 或 false.但是，似乎无论我做什么，SQL Server (2008) 都认为我的位列可能以某种方式为空.

I'm trying to create a view where I want a column to be only true or false. However, it seems that no matter what I do, SQL Server (2008) believes my bit column can somehow be null.

我有一个名为Product"的表，其中列Status"为 INT, NULL.在视图中，我想为 Product 中的每一行返回一行，如果 Product.Status 列等于 3，则 BIT 列设置为 true，否则位字段应为 false.

I have a table called "Product" with the column "Status" which is INT, NULL. In a view, I want to return a row for each row in Product, with a BIT column set to true if the Product.Status column is equal to 3, otherwise the bit field should be false.

示例 SQL

SELECT CAST( CASE ISNULL(Status, 0)  
               WHEN 3 THEN 1  
               ELSE 0  
             END AS bit) AS HasStatus  
FROM dbo.Product  

如果我将此查询保存为视图并查看对象资源管理器中的列，则 HasStatus 列将设置为 BIT, NULL.但它永远不应该是 NULL.是否有一些神奇的 SQL 技巧可以用来强制此列 NOT NULL.

If I save this query as a view and look at the columns in Object Explorer, the column HasStatus is set to BIT, NULL. But it should never be NULL. Is there some magic SQL trick I can use to force this column to be NOT NULL.

请注意，如果我删除 CASE 周围的 CAST()，该列将正确设置为 NOT NULL，但随后列的类型设置为 INT，这不是我想要的.我希望它是 BIT.:-)

Notice that, if I remove the CAST() around the CASE, the column is correctly set as NOT NULL, but then the column's type is set to INT, which is not what I want. I want it to be BIT. :-)

推荐答案

您可以通过稍微重新安排您的查询来实现您想要的.诀窍是 ISNULL 必须在外部，然后 SQL Server 才能理解结果值永远不会是 NULL.

You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULL has to be on the outside before SQL Server will understand that the resulting value can never be NULL.

SELECT ISNULL(CAST(
    CASE Status
        WHEN 3 THEN 1  
        ELSE 0  
    END AS bit), 0) AS HasStatus  
FROM dbo.Product  

我认为这很有用的一个原因是当使用 ORM 而你没有想要将结果值映射到可空类型.如果您的应用程序认为该值永远不可能为空，则可以使事情变得更容易.那你就不用写代码来处理空异常等了

One reason I actually find this useful is when using an ORM and you do not want the resulting value mapped to a nullable type. It can make things easier all around if your application sees the value as never possibly being null. Then you don't have to write code to handle null exceptions, etc.

这篇关于如何使视图列 NOT NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！