SQL 如何在 NVARCHAR 列上约束没有空字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5489648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to constraint no empty strings on an NVARCHAR column
提问by Mazen Elkashef
I add a NOT NULLconstraint on my NVARCHARcolumn so it couldn't allow any empty values. but unfortunately SQL Server deals with NULLand empty values as two separate values!
我NOT NULL在我的NVARCHAR列上添加了一个约束，所以它不能允许任何空值。但不幸的是，SQL Server 将NULL空值作为两个单独的值处理！
So how do I make the server throw an exception when inserting nothing to the column?
那么如何在不向列中插入任何内容时让服务器抛出异常呢？
I'm thinking using the constraint CHECKbut I didn't find any samples when used with NVARCHARcolumns!
我正在考虑使用约束CHECK但在与NVARCHAR列一起使用时我没有找到任何样本！
回答by tvanfosson
You could add a check constraint that ensures that the string isn't empty.
您可以添加检查约束以确保字符串不为空。
CREATE TABLE [dbo].[Foo](
[bar] [nvarchar](50) NOT NULL
)
ALTER TABLE [dbo].[Foo] WITH CHECK
ADD CONSTRAINT [CK_Foo] CHECK (([bar]<>N''))
回答by ntziolis
Use a check constraint:
使用检查约束：
CREATE TABLE SomeTable(
SomeColumn VARCHAR(50) NOT NULL CHECK (SomeColumn <> '')
)
回答by Cos Callis
- If the value is being assigned a string property you can add logic at the property set method.
- If you would rather enforce this at the SQL layer then tvanfosson's answer is good.
- Unfortunately using a trigger is not going to work because a trigger can not affect the record being inserted...
- 如果该值被分配了一个字符串属性，您可以在属性设置方法中添加逻辑。
- 如果您更愿意在 SQL 层强制执行此操作，那么 tvanfosson 的回答很好。
- 不幸的是，使用触发器是行不通的，因为触发器不能影响正在插入的记录......
回答by Tejs
回答by KeithS
How about a check constraint that the column value doesn't equal an empty string? A bit cleaner than a trigger, and it can be modeled in code using many ORMs including NHibernate, so your app layer can be told to expect it.
列值不等于空字符串的检查约束怎么样？比触发器更简洁，并且可以使用包括 NHibernate 在内的许多 ORM 在代码中建模，因此可以告诉您的应用程序层期待它。
Failing that, when assigning the field you never want to be empty up in your application layer, try using a NullIfBlank() extension method:
否则，在分配您永远不想在应用程序层清空的字段时，请尝试使用 NullIfBlank() 扩展方法：
public static string NullIfBlank(this string input)
{
return String.IsNullOrWhitespace(input) ? null : input;
}
Null strings, empty strings, and strings that only contain spaces, newlines, tabspaces, etc will all come back as null. Now, this will require you to make sure you aren't doing any simple concatenations with this field that you don't also want to come back null: null + "any string" == null.
空字符串、空字符串和仅包含空格、换行符、制表符等的字符串都将返回为 null。现在，这将要求您确保您没有对该字段进行任何简单的连接，您也不想返回 null：null + "any string" == null。
回答by dkretz
It would be nice if MSSQL provided a clean way to configure empty strings to map to NULL, when that's the meaning in context. But the database is not the best place to be trapping this since there's no error handling without raising an error to another abstraction level where it should have been handled in the first place.
如果 MSSQL 提供了一种干净的方法来配置空字符串以映射到 NULL，那就太好了，这就是上下文中的含义。但是数据库并不是捕获它的最佳位置，因为没有错误处理，除非将错误提升到另一个抽象级别，在那里它应该首先被处理。
