Normally is it good practice to set all database columns as NOT NULL or not ? Justify your answer.
通常，将所有数据库列设置为非空或非空是一种好习惯吗?证明你的答案。
17 个解决方案
#1
36
No. It's a good idea to set columns to NULL where appropriate.
不。最好将列设置为NULL。
#2
30
I kind of disagree with the "where appropriate" rule. It is actually rather safe to set any column to be NOT NULL; and then later modify the columns to allow NULL values when you need them. On the other hand, if you allow NULL values first and then later decide you don't want to allow them, it can potentially be much more difficult to do this.
我有点不同意“在适当的时候”的规则。实际上，将任何列设置为非空是相当安全的;然后在需要时修改列以允许空值。另一方面，如果您首先允许空值，然后决定不允许它们，那么这样做可能会困难得多。
It may make your database table/column descriptions quite ugly if you do this excessively, but when in doubt, go ahead and restrict the data.
如果过度地这样做，可能会使数据库表/列描述变得非常难看，但是如果有疑问，请继续限制数据。
#3
11
Relational theory has it that NULL is evil.
关系理论认为零是邪恶的。
However, your question kind of referred to practice.
但是，你的问题有点像实践。
So, to the extent that you want your practices to conform to the heavenly ideals of theory, yes, avoid NULL as if it were the plague, Cholera and AIDS all-in-one.
所以，如果你想让你的实践符合天上理想的理论，是的，避免无效，就像瘟疫，霍乱和艾滋病一样。
To the extent that these crappy implementations called "SQL DBMSs" do not leave you any other choice, yes, (sniff) use them.
如果这些糟糕的实现称为“SQL DBMSs”，那么您就不会有其他选择，是的，(嗅探)使用它们。
EDIT
编辑
Someone mentioned "business rules" as the guideline for "appropriateness" in the accepted answer, and some others upvoted that remark. That is total crap. Business rules can always do without NULLs and the only guideline to "appropriateness" is the very deficiencies of any SQL system that makes it a non-relational system to boot.
有人提到了“商业规则”作为被接受的答案中的“适当性”的指导原则，而另一些人则认为这句话是正确的。这是一坨屎。业务规则总是可以不使用null，而“合适性”的惟一指导原则是任何SQL系统的缺陷，使其成为非关系系统的启动。
#4
6
The inventor of the NULL reference (1965) recently called it his "billion-dollar mistake": http://qconlondon.com/london-2009/presentation/Null+References:+The+Billion+Dollar+Mistake
空引用(1965)的发明者最近称它为“十亿美元的错误”:http://qconlondon.com/london-2009/presentation/Null+References:+The+Billion+Dollar+ Dollar+ mistake
Languages such as Scala, SML, and Haskell are non-NULL by default: NULL is called "Option" or "Maybe" and require special syntax and checks.
Scala、SML和Haskell等语言在默认情况下是非空的:NULL被称为“选项”或“可能”，需要特殊的语法和检查。
Since the time databases were invented, allowing NULL by default has been considered more and more dangerous and undesirable. Should databases follow? Probably.
自从创建数据库以来，默认允许NULL就被认为是越来越危险和不受欢迎的。数据库应该遵循?可能。
Go with NOT NULL when you can.
当你可以的时候用NOT NULL。
#5
5
If you can't know the value at insert time, you really must have a null allowed. For instance, suppose you havea record that includes two fields, begin date and end date. You know begin date when the record is inserted but not the end date. Creating a fake date to put in this field just to avoid nulls is dumb to say the least.
如果在插入时不知道值，则必须允许空值。例如，假设您有一个包含两个字段的记录，开始日期和结束日期。您知道在插入记录时开始日期，而不是结束日期。在这个字段中创建一个假日期以避免空值，至少可以说是愚蠢的。
In real life at least as much harm is caused by forcing data entry into a field as by not forcing it. If you havea an email field and don't know the customer's email, then the user has to make something up to put into the required field. Likely what they make up may not be what you would want them to make up something like "thisistupid@ass.com". Sometimes this bad info gets provided back to the client or to a vendor in a data feed and your company looks really really stupid. I know as I process a lot of these feeds coming in from our customers. Nice things in the email field have included, "his secretary is the fat blonde", "this guy is a jerk" etc.
在现实生活中，强制数据进入字段和不强制数据进入字段造成的伤害至少是一样的。如果您有一个电子邮件字段，但不知道客户的电子邮件，那么用户必须编造一些内容来放入所需的字段中。很可能他们编造的东西可能不是你希望他们编造的“thisistupid d@ass.com”。有时这些糟糕的信息会被提供给客户或数据提要中的供应商，而你的公司看起来真的很愚蠢。我知道当我处理许多来自我们客户的提要时。电子邮件领域的好事包括:“他的秘书是个胖金发美女”、“这家伙是个混蛋”等等。
#6
4
From my perspective, while it may be better for the database, it's not better for the user. Once you get into more interactive applications, you want to be able to persist the data in an interim state, so most of your fields will probably be null at that point.
在我看来，虽然它可能对数据库更好，但对用户来说却不是更好。一旦您进入了更具交互性的应用程序，您希望能够将数据持久化到一个临时状态，因此此时您的大多数字段可能都是空的。
#7
2
It depends on what you're trying to do, but for many applications it's a good idea to avoid NULLs where possible — and the most foolproof way to do this is to use NOT NULL.
这取决于您想要做什么，但是对于许多应用程序来说，避免在可能的情况下使用NULL是一个好主意——而且最简单的方法是使用NOT NULL。
The problem is that the meaning of NULL is open to interpretation. It could mean “no value belongs here,” or it could mean “we haven't got the value yet, so we should keep asking the user for it.” If you are using it, you'll want to read up on SQL's 3-valued logic, and functions such as COALESCE, etc.
问题是，NULL的含义是可以解释的。它可以表示“没有值属于这里”，也可以表示“我们还没有得到值，所以我们应该继续向用户索要它”。如果您正在使用它，您将希望阅读SQL的3值逻辑，以及诸如COALESCE等函数。
Nevertheless, as Cletus and others have said, if you use NULL appropriately it can be useful.
尽管如此，正如Cletus和其他人所说，如果您适当地使用NULL，它是有用的。
#8
2
In business apps I was always removing my NOT NULLS because the users did not like being forced to enter data that they didn't know. It depends on the table but I set most of my fields to NULL and only set the bare minimum number of fields to NOT NULL.
在商业应用中，我总是删除NOT NULLS，因为用户不喜欢*输入他们不知道的数据。它取决于表，但我将大部分字段设置为NULL，并且只将字段的最小数量设置为NOT NULL。
#9
2
I'm a newbie and my answer may be totally asinine, but here's my personal take on the subject.
我是一个新手，我的回答可能完全是愚蠢的，但这是我个人对这个问题的看法。
In my humble opinion, I don't see the problem with allowing ALL fields except primary/foreign keys to be nullable. I know many of you cringed as soon as I said that, and I'm sure I heard someone cry out, "Heretic! Burn him at the stake!" But here's my reasoning:
在我的拙见中，我不认为允许除主/外键之外的所有字段都是空的问题。我知道你们很多人一听我说这话就畏缩了，我肯定我听到有人大喊:“异端!”把他烧死在火刑柱上!但这是我的推理:
Is it really the job of the database to enforce rules about what values should and should not be permitted - except of course as needed to enforce things like referential integrity and to control storage consumption (by having things like max chars set)? Wouldn't it be easier and better to enforce all "null vs. not null" rules at the code level prior to storing the values in the database?
是否真的是数据库的工作来执行关于哪些值应该和不应该被允许的规则——当然，除了需要强制执行诸如引用完整性和控制存储消耗(通过使用max chars集之类的东西)?在数据库中存储值之前，在代码级别强制执行所有“null / not null”规则不是更容易、更好吗?
After all, it's the job of the code to validate all values prior to them being stored in the database anyway, right? So why should the database try to usurp the code's authority by also setting up rules about what values are valid? (In a way, using not null constraints except where absolutely necessary almost feels like a violation of the idea of "separation of concerns.") Furthermore, any time a constraint is enforced at the database level, it must necessarily be enforced at the code level also to prevent the code from "blowing up." So why do twice as much work?
毕竟，代码的工作就是在所有值被存储到数据库之前验证它们，对吗?那么，为什么数据库还要试图通过建立关于哪些值是有效的规则来篡夺代码的权威呢?(在某种程度上,使用not null约束除非绝对必要的想法几乎就像是一种违反“关注点分离”)。此外,任何时候一个约束是在数据库级别上执行,它也一定会在代码级别执行防止代码“爆炸”。那么为什么要做两倍的工作呢?
At least for me, it seems like things work out better when my database is allowed to simply be a "dumb data storage container" because inevitably in the past when I've tried to use "NOT NULL" to enforce a business rule which made sense to me at the time, I end up wishing I hadn't and end up going back and removing the constraint.
至少对我来说,似乎事情解决好当我的数据库可以仅仅是一个“愚蠢的数据存储容器”,因为不可避免的在过去当我试图用“不空”执行业务规则,当时对我有意义,最终我希望我没有,最终返回和删除约束。
Like I said, I realize I'm a newbie and if there's something I'm overlooking, let me know - and try not to butcher me up too bad :) Thanks.
就像我说的，我意识到我是一个新手，如果有什么我可以忽略的东西，让我知道——并且尽量不要把我搞得太糟:)谢谢。
#10
1
If your data can actually BE "unknown", and it's important to record that fact, then yes, use a NULL. Bear in mind that sometimes you need to differentiate between "unknown" and "not relevant" - for example, a DateTime field in one of my databases can either be the SQL Server minimum date (not applicable), NULL (unknown), or any other date (known value).
如果您的数据实际上可能是“未知的”，并且记录这个事实很重要，那么请使用NULL。请记住，有时您需要区分“未知”和“无关”——例如，我的数据库中的一个DateTime字段可以是SQL Server最小日期(不适用)、NULL(未知)或任何其他日期(已知值)。
For fields which don't really have business rules depending on them - I'm talking about "Comments", "Description", "Notes" columns here - then I set them to default to empty strings, as (a) it saves dealing with nulls, and (b) they are never "unknown" - they just aren't filled in, which logically is a known empty value.
的字段没有根据业务规则——我说的是“评论”、“描述”,“笔记”列在这里,那么我将默认为空字符串,如(一)保存处理null,和(b)他们从未被“未知”,他们只是没有填写,这在逻辑上是一个空值。
E.g.:
例如:
CREATE TABLE Computer (
Id INT IDENTITY PRIMARY KEY
, Name NVARCHAR(16) NOT NULL
, ...[other fields]...
, Comments NVARCHAR(255) NOT NULL
CONSTRAINT DF_Computer_Comments DEFAULT (N'')
)
If you don't supply a value to Comments, it defaults to empty.
如果不为注释提供值，则默认为空。
#11
0
Short answer: it depends on what you are storing.
简短的回答:这取决于你储存的是什么。
I can see a table (or two) having all NOT NULLS or all NULLS. But an entire database?
我可以看到一个表(或两个)包含所有非空值或所有空值。但整个数据库?
#12
0
Only for columns where not having a value doesn't make any sense.
只有那些没有值的列才没有意义。
Nulls can be very handy; for one thing, they compress beautifully. They can be a nasty surprise when you don't expect them, though, so if you can't have a Student without a First Name -- make that column NOT NULL. (Middle names, on the other hand... maybe you want to have a default empty string, maybe not -- decent arguments both ways)
Nulls非常方便;首先，它们压缩得很好。但是，如果您不期望它们出现，它们可能会是一个令人讨厌的惊喜，因此，如果您不能让一个学生没有名字，那么请将该列设为NOT NULL。(中间的名字，另一方面……)也许你想要一个默认的空字符串，也许不是，两种方法都可以)
#13
0
You should not forget to set not null where needed, use check constraints if applicable, not forget about unique constraints, create proper indexes and brush your teeth after every meal and before going to bed:)
您不应该忘记在需要的地方设置not null，如果可以，使用检查约束，不要忘记惟一约束，创建适当的索引，每次饭后和睡前刷牙:)
In most cases you can use not null and you should use not null. It is easier to change not null->null than in opposite direction, but for example in Oracle empty string is treated as null, so it is obvious that you can't use it all the time.
在大多数情况下，可以使用not null，应该使用not null。将not null->改为非空值要比在相反方向更容易，但是例如，在Oracle中，空字符串被视为空，因此很明显，您不能一直使用它。
#14
0
What's the alternative?
另一种是什么?
I found this question as a result of a discussion at work. Our question was:
我是在工作中讨论后发现这个问题的。我们的问题是:
Should we have a nullable foreign key or an association table with unique constraints? The context was that sometimes there is an association and sometimes there isn't. (EG: Unplanned vs. planned schedules)
我们应该拥有一个可空的外键还是具有唯一约束的关联表?背景是，有时候有关联，有时候没有。(例如:计划外计划和计划外计划)
For me, a combination of nullable foreign key with a 'set field to null on delete' was equivalent to the association table but had two advantages:
对我来说，将可空外键与“set字段在delete上为null”组合在一起相当于关联表，但有两个优点:
- More understandable (the schema was already complex)
- 更容易理解(模式已经很复杂)
- Easier to find 'unplanned' schedules with an 'xxx is null' query (vs. not exists query)
- 使用“xxx是null”查询(不存在查询)更容易找到“计划外”的日程安排
In summary, sometimes 'null' (the absence of information) actually means something. Try to have non-null, but there are exceptions.
总而言之，有时‘null’(没有信息)实际上是有意义的。尽量使用非空，但也有例外。
FWIW, we were using Scala / Squeryl so, in code, the field was an 'Option' and quite safe.
FWIW，我们使用的是Scala / Squeryl，所以在代码中，字段是一个“选项”，非常安全。
#15
0
My take is that if you want to have flexible and "ambiguous" tables to some extent, just use NoSQL, as it is precisely built for that purpose. Otherwise, having a NULL value in a row is just acceptable as it maybe some piece of optional data, like Address 2, or home phone number and that kind of things.
我的观点是，如果您想在某种程度上拥有灵活且“不明确”的表，只需使用NoSQL，因为它正是为此目的而构建的。否则，行中有NULL值是可以接受的，因为它可能是一些可选数据，比如地址2，或者家庭电话号码等等。
In my opinion, making Foreign keys nullable break one of the main reasons we use relational databases. As you want your data to be as tightly related and consistent as possible.
在我看来，使外键可空中断是我们使用关系数据库的主要原因之一。您希望您的数据尽可能紧密地相关和一致。
#16
0
It depends (on the datatype)
它依赖于数据类型
Think about this, If the immediate technology that interacts with database is Python I shall make everything NOT NULL with a proper DEFAULT.
考虑到这一点，如果与数据库交互的即时技术是Python，我将使用适当的默认值使所有内容都不为空。
However the above makes sense if the column is VARCHAR with default as empty string.
但是，如果列是VARCHAR，默认为空字符串，那么上面的操作是有意义的。
What about NUMERIC, It is hard to come up with default values where NULL can convey more details other than simply set to DEFAULT=0
对于数值来说，除了简单地设置为default =0之外，很难找到空值可以传递更多的细节
For BOOLEAN still NULL makes some sense, and so on.
对于布尔值仍然为NULL是有意义的，等等。
Similar argument can be carried out for various datatypes like spatial data types.
类似的参数可以用于各种数据类型，如空间数据类型。
#17
-2
IMO, using NULLable option must be minimized. The application should designate a suitable value for the "non-existent" state. In Peoplesoft I think, the application puts a 0 for Numericals and a space for Char columns where a value does not exist.
在我看来，使用可空选项必须最小化。应用程序应该为“不存在”的状态指定一个合适的值。在Peoplesoft中，我认为应用程序为数字加0，为不存在值的Char列加空格。
One could argue why the so-called suitable value couldn't be NULL.
人们可能会争论为什么所谓的合适价值不可能是零。
Because SQL implementation treats nulls totally differently.
因为SQL实现对nulls的处理完全不同。
For e.g. 1 = NULL and 0 = NULL both result in false! NULL = NULL is false! NULL value in GROUP BY and other aggregate functions also create unexpected results.
例如，1 = NULL和0 = NULL都会导致false!NULL是假的!GROUP BY和其他聚合函数中的NULL值也会产生意想不到的结果。
#1
36
No. It's a good idea to set columns to NULL where appropriate.
不。最好将列设置为NULL。
#2
30
I kind of disagree with the "where appropriate" rule. It is actually rather safe to set any column to be NOT NULL; and then later modify the columns to allow NULL values when you need them. On the other hand, if you allow NULL values first and then later decide you don't want to allow them, it can potentially be much more difficult to do this.
我有点不同意“在适当的时候”的规则。实际上，将任何列设置为非空是相当安全的;然后在需要时修改列以允许空值。另一方面，如果您首先允许空值，然后决定不允许它们，那么这样做可能会困难得多。
It may make your database table/column descriptions quite ugly if you do this excessively, but when in doubt, go ahead and restrict the data.
如果过度地这样做，可能会使数据库表/列描述变得非常难看，但是如果有疑问，请继续限制数据。
#3
11
Relational theory has it that NULL is evil.
关系理论认为零是邪恶的。
However, your question kind of referred to practice.
但是，你的问题有点像实践。
So, to the extent that you want your practices to conform to the heavenly ideals of theory, yes, avoid NULL as if it were the plague, Cholera and AIDS all-in-one.
所以，如果你想让你的实践符合天上理想的理论，是的，避免无效，就像瘟疫，霍乱和艾滋病一样。
To the extent that these crappy implementations called "SQL DBMSs" do not leave you any other choice, yes, (sniff) use them.
如果这些糟糕的实现称为“SQL DBMSs”，那么您就不会有其他选择，是的，(嗅探)使用它们。
EDIT
编辑
Someone mentioned "business rules" as the guideline for "appropriateness" in the accepted answer, and some others upvoted that remark. That is total crap. Business rules can always do without NULLs and the only guideline to "appropriateness" is the very deficiencies of any SQL system that makes it a non-relational system to boot.
有人提到了“商业规则”作为被接受的答案中的“适当性”的指导原则，而另一些人则认为这句话是正确的。这是一坨屎。业务规则总是可以不使用null，而“合适性”的惟一指导原则是任何SQL系统的缺陷，使其成为非关系系统的启动。
#4
6
The inventor of the NULL reference (1965) recently called it his "billion-dollar mistake": http://qconlondon.com/london-2009/presentation/Null+References:+The+Billion+Dollar+Mistake
空引用(1965)的发明者最近称它为“十亿美元的错误”:http://qconlondon.com/london-2009/presentation/Null+References:+The+Billion+Dollar+ Dollar+ mistake
Languages such as Scala, SML, and Haskell are non-NULL by default: NULL is called "Option" or "Maybe" and require special syntax and checks.
Scala、SML和Haskell等语言在默认情况下是非空的:NULL被称为“选项”或“可能”，需要特殊的语法和检查。
Since the time databases were invented, allowing NULL by default has been considered more and more dangerous and undesirable. Should databases follow? Probably.
自从创建数据库以来，默认允许NULL就被认为是越来越危险和不受欢迎的。数据库应该遵循?可能。
Go with NOT NULL when you can.
当你可以的时候用NOT NULL。
#5
5
If you can't know the value at insert time, you really must have a null allowed. For instance, suppose you havea record that includes two fields, begin date and end date. You know begin date when the record is inserted but not the end date. Creating a fake date to put in this field just to avoid nulls is dumb to say the least.
如果在插入时不知道值，则必须允许空值。例如，假设您有一个包含两个字段的记录，开始日期和结束日期。您知道在插入记录时开始日期，而不是结束日期。在这个字段中创建一个假日期以避免空值，至少可以说是愚蠢的。
In real life at least as much harm is caused by forcing data entry into a field as by not forcing it. If you havea an email field and don't know the customer's email, then the user has to make something up to put into the required field. Likely what they make up may not be what you would want them to make up something like "thisistupid@ass.com". Sometimes this bad info gets provided back to the client or to a vendor in a data feed and your company looks really really stupid. I know as I process a lot of these feeds coming in from our customers. Nice things in the email field have included, "his secretary is the fat blonde", "this guy is a jerk" etc.
在现实生活中，强制数据进入字段和不强制数据进入字段造成的伤害至少是一样的。如果您有一个电子邮件字段，但不知道客户的电子邮件，那么用户必须编造一些内容来放入所需的字段中。很可能他们编造的东西可能不是你希望他们编造的“thisistupid d@ass.com”。有时这些糟糕的信息会被提供给客户或数据提要中的供应商，而你的公司看起来真的很愚蠢。我知道当我处理许多来自我们客户的提要时。电子邮件领域的好事包括:“他的秘书是个胖金发美女”、“这家伙是个混蛋”等等。
#6
4
From my perspective, while it may be better for the database, it's not better for the user. Once you get into more interactive applications, you want to be able to persist the data in an interim state, so most of your fields will probably be null at that point.
在我看来，虽然它可能对数据库更好，但对用户来说却不是更好。一旦您进入了更具交互性的应用程序，您希望能够将数据持久化到一个临时状态，因此此时您的大多数字段可能都是空的。
#7
2
It depends on what you're trying to do, but for many applications it's a good idea to avoid NULLs where possible — and the most foolproof way to do this is to use NOT NULL.
这取决于您想要做什么，但是对于许多应用程序来说，避免在可能的情况下使用NULL是一个好主意——而且最简单的方法是使用NOT NULL。
The problem is that the meaning of NULL is open to interpretation. It could mean “no value belongs here,” or it could mean “we haven't got the value yet, so we should keep asking the user for it.” If you are using it, you'll want to read up on SQL's 3-valued logic, and functions such as COALESCE, etc.
问题是，NULL的含义是可以解释的。它可以表示“没有值属于这里”，也可以表示“我们还没有得到值，所以我们应该继续向用户索要它”。如果您正在使用它，您将希望阅读SQL的3值逻辑，以及诸如COALESCE等函数。
Nevertheless, as Cletus and others have said, if you use NULL appropriately it can be useful.
尽管如此，正如Cletus和其他人所说，如果您适当地使用NULL，它是有用的。
#8
2
In business apps I was always removing my NOT NULLS because the users did not like being forced to enter data that they didn't know. It depends on the table but I set most of my fields to NULL and only set the bare minimum number of fields to NOT NULL.
在商业应用中，我总是删除NOT NULLS，因为用户不喜欢*输入他们不知道的数据。它取决于表，但我将大部分字段设置为NULL，并且只将字段的最小数量设置为NOT NULL。
#9
2
I'm a newbie and my answer may be totally asinine, but here's my personal take on the subject.
我是一个新手，我的回答可能完全是愚蠢的，但这是我个人对这个问题的看法。
In my humble opinion, I don't see the problem with allowing ALL fields except primary/foreign keys to be nullable. I know many of you cringed as soon as I said that, and I'm sure I heard someone cry out, "Heretic! Burn him at the stake!" But here's my reasoning:
在我的拙见中，我不认为允许除主/外键之外的所有字段都是空的问题。我知道你们很多人一听我说这话就畏缩了，我肯定我听到有人大喊:“异端!”把他烧死在火刑柱上!但这是我的推理:
Is it really the job of the database to enforce rules about what values should and should not be permitted - except of course as needed to enforce things like referential integrity and to control storage consumption (by having things like max chars set)? Wouldn't it be easier and better to enforce all "null vs. not null" rules at the code level prior to storing the values in the database?
是否真的是数据库的工作来执行关于哪些值应该和不应该被允许的规则——当然，除了需要强制执行诸如引用完整性和控制存储消耗(通过使用max chars集之类的东西)?在数据库中存储值之前，在代码级别强制执行所有“null / not null”规则不是更容易、更好吗?
After all, it's the job of the code to validate all values prior to them being stored in the database anyway, right? So why should the database try to usurp the code's authority by also setting up rules about what values are valid? (In a way, using not null constraints except where absolutely necessary almost feels like a violation of the idea of "separation of concerns.") Furthermore, any time a constraint is enforced at the database level, it must necessarily be enforced at the code level also to prevent the code from "blowing up." So why do twice as much work?
毕竟，代码的工作就是在所有值被存储到数据库之前验证它们，对吗?那么，为什么数据库还要试图通过建立关于哪些值是有效的规则来篡夺代码的权威呢?(在某种程度上,使用not null约束除非绝对必要的想法几乎就像是一种违反“关注点分离”)。此外,任何时候一个约束是在数据库级别上执行,它也一定会在代码级别执行防止代码“爆炸”。那么为什么要做两倍的工作呢?
At least for me, it seems like things work out better when my database is allowed to simply be a "dumb data storage container" because inevitably in the past when I've tried to use "NOT NULL" to enforce a business rule which made sense to me at the time, I end up wishing I hadn't and end up going back and removing the constraint.
至少对我来说,似乎事情解决好当我的数据库可以仅仅是一个“愚蠢的数据存储容器”,因为不可避免的在过去当我试图用“不空”执行业务规则,当时对我有意义,最终我希望我没有,最终返回和删除约束。
Like I said, I realize I'm a newbie and if there's something I'm overlooking, let me know - and try not to butcher me up too bad :) Thanks.
就像我说的，我意识到我是一个新手，如果有什么我可以忽略的东西，让我知道——并且尽量不要把我搞得太糟:)谢谢。
#10
1
If your data can actually BE "unknown", and it's important to record that fact, then yes, use a NULL. Bear in mind that sometimes you need to differentiate between "unknown" and "not relevant" - for example, a DateTime field in one of my databases can either be the SQL Server minimum date (not applicable), NULL (unknown), or any other date (known value).
如果您的数据实际上可能是“未知的”，并且记录这个事实很重要，那么请使用NULL。请记住，有时您需要区分“未知”和“无关”——例如，我的数据库中的一个DateTime字段可以是SQL Server最小日期(不适用)、NULL(未知)或任何其他日期(已知值)。
For fields which don't really have business rules depending on them - I'm talking about "Comments", "Description", "Notes" columns here - then I set them to default to empty strings, as (a) it saves dealing with nulls, and (b) they are never "unknown" - they just aren't filled in, which logically is a known empty value.
的字段没有根据业务规则——我说的是“评论”、“描述”,“笔记”列在这里,那么我将默认为空字符串,如(一)保存处理null,和(b)他们从未被“未知”,他们只是没有填写,这在逻辑上是一个空值。
E.g.:
例如:
CREATE TABLE Computer (
Id INT IDENTITY PRIMARY KEY
, Name NVARCHAR(16) NOT NULL
, ...[other fields]...
, Comments NVARCHAR(255) NOT NULL
CONSTRAINT DF_Computer_Comments DEFAULT (N'')
)
If you don't supply a value to Comments, it defaults to empty.
如果不为注释提供值，则默认为空。
#11
0
Short answer: it depends on what you are storing.
简短的回答:这取决于你储存的是什么。
I can see a table (or two) having all NOT NULLS or all NULLS. But an entire database?
我可以看到一个表(或两个)包含所有非空值或所有空值。但整个数据库?
#12
0
Only for columns where not having a value doesn't make any sense.
只有那些没有值的列才没有意义。
Nulls can be very handy; for one thing, they compress beautifully. They can be a nasty surprise when you don't expect them, though, so if you can't have a Student without a First Name -- make that column NOT NULL. (Middle names, on the other hand... maybe you want to have a default empty string, maybe not -- decent arguments both ways)
Nulls非常方便;首先，它们压缩得很好。但是，如果您不期望它们出现，它们可能会是一个令人讨厌的惊喜，因此，如果您不能让一个学生没有名字，那么请将该列设为NOT NULL。(中间的名字，另一方面……)也许你想要一个默认的空字符串，也许不是，两种方法都可以)
#13
0
You should not forget to set not null where needed, use check constraints if applicable, not forget about unique constraints, create proper indexes and brush your teeth after every meal and before going to bed:)
您不应该忘记在需要的地方设置not null，如果可以，使用检查约束，不要忘记惟一约束，创建适当的索引，每次饭后和睡前刷牙:)
In most cases you can use not null and you should use not null. It is easier to change not null->null than in opposite direction, but for example in Oracle empty string is treated as null, so it is obvious that you can't use it all the time.
在大多数情况下，可以使用not null，应该使用not null。将not null->改为非空值要比在相反方向更容易，但是例如，在Oracle中，空字符串被视为空，因此很明显，您不能一直使用它。
#14
0
What's the alternative?
另一种是什么?
I found this question as a result of a discussion at work. Our question was:
我是在工作中讨论后发现这个问题的。我们的问题是:
Should we have a nullable foreign key or an association table with unique constraints? The context was that sometimes there is an association and sometimes there isn't. (EG: Unplanned vs. planned schedules)
我们应该拥有一个可空的外键还是具有唯一约束的关联表?背景是，有时候有关联，有时候没有。(例如:计划外计划和计划外计划)
For me, a combination of nullable foreign key with a 'set field to null on delete' was equivalent to the association table but had two advantages:
对我来说，将可空外键与“set字段在delete上为null”组合在一起相当于关联表，但有两个优点:
- More understandable (the schema was already complex)
- 更容易理解(模式已经很复杂)
- Easier to find 'unplanned' schedules with an 'xxx is null' query (vs. not exists query)
- 使用“xxx是null”查询(不存在查询)更容易找到“计划外”的日程安排
In summary, sometimes 'null' (the absence of information) actually means something. Try to have non-null, but there are exceptions.
总而言之，有时‘null’(没有信息)实际上是有意义的。尽量使用非空，但也有例外。
FWIW, we were using Scala / Squeryl so, in code, the field was an 'Option' and quite safe.
FWIW，我们使用的是Scala / Squeryl，所以在代码中，字段是一个“选项”，非常安全。
#15
0
My take is that if you want to have flexible and "ambiguous" tables to some extent, just use NoSQL, as it is precisely built for that purpose. Otherwise, having a NULL value in a row is just acceptable as it maybe some piece of optional data, like Address 2, or home phone number and that kind of things.
我的观点是，如果您想在某种程度上拥有灵活且“不明确”的表，只需使用NoSQL，因为它正是为此目的而构建的。否则，行中有NULL值是可以接受的，因为它可能是一些可选数据，比如地址2，或者家庭电话号码等等。
In my opinion, making Foreign keys nullable break one of the main reasons we use relational databases. As you want your data to be as tightly related and consistent as possible.
在我看来，使外键可空中断是我们使用关系数据库的主要原因之一。您希望您的数据尽可能紧密地相关和一致。
#16
0
It depends (on the datatype)
它依赖于数据类型
Think about this, If the immediate technology that interacts with database is Python I shall make everything NOT NULL with a proper DEFAULT.
考虑到这一点，如果与数据库交互的即时技术是Python，我将使用适当的默认值使所有内容都不为空。
However the above makes sense if the column is VARCHAR with default as empty string.
但是，如果列是VARCHAR，默认为空字符串，那么上面的操作是有意义的。
What about NUMERIC, It is hard to come up with default values where NULL can convey more details other than simply set to DEFAULT=0
对于数值来说，除了简单地设置为default =0之外，很难找到空值可以传递更多的细节
For BOOLEAN still NULL makes some sense, and so on.
对于布尔值仍然为NULL是有意义的，等等。
Similar argument can be carried out for various datatypes like spatial data types.
类似的参数可以用于各种数据类型，如空间数据类型。
#17
-2
IMO, using NULLable option must be minimized. The application should designate a suitable value for the "non-existent" state. In Peoplesoft I think, the application puts a 0 for Numericals and a space for Char columns where a value does not exist.
在我看来，使用可空选项必须最小化。应用程序应该为“不存在”的状态指定一个合适的值。在Peoplesoft中，我认为应用程序为数字加0，为不存在值的Char列加空格。
One could argue why the so-called suitable value couldn't be NULL.
人们可能会争论为什么所谓的合适价值不可能是零。
Because SQL implementation treats nulls totally differently.
因为SQL实现对nulls的处理完全不同。
For e.g. 1 = NULL and 0 = NULL both result in false! NULL = NULL is false! NULL value in GROUP BY and other aggregate functions also create unexpected results.
例如，1 = NULL和0 = NULL都会导致false!NULL是假的!GROUP BY和其他聚合函数中的NULL值也会产生意想不到的结果。