What general guidelines should I go by when considering whether I should mark a field 'not null' as opposed to just declaring everything but the primary key null?
在考虑是否应该将字段标记为“非空”而不仅仅声明除主键之外的所有内容时,我应该遵循哪些一般指导原则?
Should 'not null' fields have DEFAULT values?
“not null”字段是否应具有DEFAULT值?
5 个解决方案
#1
Depends on how you want your application to behave.
取决于您希望应用程序的行为方式。
- First, there will never ever ever be a possible row where this value does NOT contain meaningful data, then use NOT NULL. That is, this value will always be meaningful and represent something.
- Do you want the value to always be filled out by the user or programmer in some form or fashion? Use NOT NULL without a DEFAULT
- Do you want it to be optional to users and programmers? Use NOT NULL with a DEFAULT
首先,永远不会有一个可能的行,其中该值不包含有意义的数据,然后使用NOT NULL。也就是说,这个值总是有意义并代表某些东西。
您是否希望用户或程序员以某种形式或方式填写该值?在没有DEFAULT的情况下使用NOT NULL
您希望它对用户和程序员来说是可选的吗?对DEFAULT使用NOT NULL
#2
I think you've got 2 questions there:
我想你有2个问题:
Should you mark fields as not null?
你应该将字段标记为非空吗?
Yes, assuming that you never intend a valid row to have a null value in that field. Think of "not null" as the easiest type of constraint you can put on a field. Constraints in a database help ensure the data is kept consistent by meeting expectations.
是的,假设您从未打算有效行在该字段中具有空值。可以将“not null”视为可以放在字段上的最简单的约束类型。数据库中的约束有助于确保数据通过满足期望保持一致。
Should not null fields have defaults?
null字段是否应该有默认值?
Only when there is an obvious default. For example the Paid field of an invoices table might have a default of 0 (false). In general it works the other way around - if a field has a default value, then it should probably also be not null.
只有当有明显的默认值时。例如,发票表的付费字段可能默认为0(false)。通常它反过来工作 - 如果一个字段有一个默认值,那么它应该也可能不是null。
Don't create defaults just for the sake of defaults - if a field should not be null, but there isn't a universal default, then leave it be. That ensures that any INSERT statements must provide a value for that field.
不要仅仅为了默认值而创建默认值 - 如果一个字段不应该为null,但是没有通用默认值,那么请保留默认值。这可确保任何INSERT语句都必须为该字段提供值。
#3
A lot of people look down upon so-called "magic numbers" and would advocate leaving the field as null instead of putting a default down. The only time I ever use default values is when I have a bit field and I just want it to default to false.
很多人都瞧不起所谓的“神奇数字”,并主张将该领域留空而不是默认下来。我唯一使用默认值的时候是我有一个位字段,我只想让它默认为false。
#4
Do what is semantically correct.
做什么是语义正确的。
- NULL - does not always exist
- NOT NULL - always exists
NULL - 并不总是存在
NOT NULL - 始终存在
Try not to define and persist artificial values, like "No value selected" for a drop-down field, or a "No Manager" for an employee's manager.
尽量不要定义和保留人工值,例如下拉字段中的“无值选择”或员工经理的“无经理”。
Whether to use defaults depends on how data gets inserted. If there is a UI with validation, you don't need defaults, IMHO.
是否使用默认值取决于数据的插入方式。如果有一个带验证的UI,则不需要默认值,恕我直言。
#5
Not null fields need to have defaults if they are appropriate especially if you are adding a new field to the table (or changing the field from allowing nulls to not allowing nulls) and you need to give a value to all existing records. However a default is not appropriate or available for all possible fields. For instance, we have a person table, lastname is not allowed to be null. There is no default lastname we could assign though, if the person doesn't have a name, the record doesn't get created. On the other hand, you might have a DateCreated field with a default value of the current date. This is also a field that you would want to have as not null and you would want to make sure that the current date was put in whether the record was inserted from the user interface or from an import or from the query window.
非空字段需要具有默认值(如果它们是合适的,特别是如果要向表中添加新字段(或将字段从允许空值更改为不允许空值),并且您需要为所有现有记录赋值。但是,默认值不适用于所有可能的字段。例如,我们有一个person表,lastname不允许为null。虽然没有我们可以分配的默认姓氏,但如果此人没有姓名,则不会创建记录。另一方面,您可能有一个DateCreated字段,其默认值为当前日期。这也是一个您希望不为null的字段,您可能希望确保当前日期是否已从用户界面或导入或查询窗口插入记录。
#1
Depends on how you want your application to behave.
取决于您希望应用程序的行为方式。
- First, there will never ever ever be a possible row where this value does NOT contain meaningful data, then use NOT NULL. That is, this value will always be meaningful and represent something.
- Do you want the value to always be filled out by the user or programmer in some form or fashion? Use NOT NULL without a DEFAULT
- Do you want it to be optional to users and programmers? Use NOT NULL with a DEFAULT
首先,永远不会有一个可能的行,其中该值不包含有意义的数据,然后使用NOT NULL。也就是说,这个值总是有意义并代表某些东西。
您是否希望用户或程序员以某种形式或方式填写该值?在没有DEFAULT的情况下使用NOT NULL
您希望它对用户和程序员来说是可选的吗?对DEFAULT使用NOT NULL
#2
I think you've got 2 questions there:
我想你有2个问题:
Should you mark fields as not null?
你应该将字段标记为非空吗?
Yes, assuming that you never intend a valid row to have a null value in that field. Think of "not null" as the easiest type of constraint you can put on a field. Constraints in a database help ensure the data is kept consistent by meeting expectations.
是的,假设您从未打算有效行在该字段中具有空值。可以将“not null”视为可以放在字段上的最简单的约束类型。数据库中的约束有助于确保数据通过满足期望保持一致。
Should not null fields have defaults?
null字段是否应该有默认值?
Only when there is an obvious default. For example the Paid field of an invoices table might have a default of 0 (false). In general it works the other way around - if a field has a default value, then it should probably also be not null.
只有当有明显的默认值时。例如,发票表的付费字段可能默认为0(false)。通常它反过来工作 - 如果一个字段有一个默认值,那么它应该也可能不是null。
Don't create defaults just for the sake of defaults - if a field should not be null, but there isn't a universal default, then leave it be. That ensures that any INSERT statements must provide a value for that field.
不要仅仅为了默认值而创建默认值 - 如果一个字段不应该为null,但是没有通用默认值,那么请保留默认值。这可确保任何INSERT语句都必须为该字段提供值。
#3
A lot of people look down upon so-called "magic numbers" and would advocate leaving the field as null instead of putting a default down. The only time I ever use default values is when I have a bit field and I just want it to default to false.
很多人都瞧不起所谓的“神奇数字”,并主张将该领域留空而不是默认下来。我唯一使用默认值的时候是我有一个位字段,我只想让它默认为false。
#4
Do what is semantically correct.
做什么是语义正确的。
- NULL - does not always exist
- NOT NULL - always exists
NULL - 并不总是存在
NOT NULL - 始终存在
Try not to define and persist artificial values, like "No value selected" for a drop-down field, or a "No Manager" for an employee's manager.
尽量不要定义和保留人工值,例如下拉字段中的“无值选择”或员工经理的“无经理”。
Whether to use defaults depends on how data gets inserted. If there is a UI with validation, you don't need defaults, IMHO.
是否使用默认值取决于数据的插入方式。如果有一个带验证的UI,则不需要默认值,恕我直言。
#5
Not null fields need to have defaults if they are appropriate especially if you are adding a new field to the table (or changing the field from allowing nulls to not allowing nulls) and you need to give a value to all existing records. However a default is not appropriate or available for all possible fields. For instance, we have a person table, lastname is not allowed to be null. There is no default lastname we could assign though, if the person doesn't have a name, the record doesn't get created. On the other hand, you might have a DateCreated field with a default value of the current date. This is also a field that you would want to have as not null and you would want to make sure that the current date was put in whether the record was inserted from the user interface or from an import or from the query window.
非空字段需要具有默认值(如果它们是合适的,特别是如果要向表中添加新字段(或将字段从允许空值更改为不允许空值),并且您需要为所有现有记录赋值。但是,默认值不适用于所有可能的字段。例如,我们有一个person表,lastname不允许为null。虽然没有我们可以分配的默认姓氏,但如果此人没有姓名,则不会创建记录。另一方面,您可能有一个DateCreated字段,其默认值为当前日期。这也是一个您希望不为null的字段,您可能希望确保当前日期是否已从用户界面或导入或查询窗口插入记录。