我们是否需要为主键指定“not null”？甲骨文/ SQL

CREATE TABLE Person(
    PersonId NUM(20),
    ...
    )

ALTER TABLE Person
ADD(CONSTRAINT personpk PRIMARY KEY(PersonId))

As title, do I need to specify "not null" for PersonId? Or if I set it to primary key, it is automatically not null by default?

作为标题，我是否需要为PersonId指定“not null”？或者，如果我将其设置为主键，默认情况下它自动不为空？

e.g: 
CREATE TABLE Person(
PersonId NUM(20) NOT NULL,
...

4 个解决方案

#1

create table mytable (
  col1 number primary key,
  col2 number,
  col3 number not null
);

table MYTABLE created.

select table_name, column_name, nullable 
from user_tab_cols where table_name = 'MYTABLE';

TABLE_NAME                     COLUMN_NAME                    NULLABLE
------------------------------ ------------------------------ --------
MYTABLE                        COL1                           N        
MYTABLE                        COL2                           Y        
MYTABLE                        COL3                           N

So, no, you do not need to specify primary key columns as NOT NULL.

所以，不，您不需要将主键列指定为NOT NULL。

#2

Yes, as @eaolson said, you don't need to specify NOT NULL for primary key columns, they are set automatically to NOT NULL.

是的，正如@eaolson所说，您不需要为主键列指定NOT NULL，它们会自动设置为NOT NULL。

However, Oracle keeps track that you didn't specify NOT NULL explicitly in case the primary key is disabled or dropped later on:

但是，Oracle会在以后禁用或删除主键时跟踪您未明确指定NOT NULL：

create table mytable (
  col1 number,
  col2 number not null
);

select table_name, column_name, nullable
  from user_tab_columns where table_name = 'MYTABLE';

TABLE_NAME   COLUMN_NAME  NULLABLE
------------ ------------ ---------
MYTABLE      COL1         Y
MYTABLE      COL2         N

As expected, col1 is nullable and col2 NOT NULL. A primary key changes both columns to NOT NULL:

正如所料，col1可以为空，col2为NULL。主键将两列都更改为NOT NULL：

alter table mytable add primary key (col1, col2);

select table_name, column_name, nullable
  from user_tab_columns where table_name = 'MYTABLE';

TABLE_NAME   COLUMN_NAME  NULLABLE
------------ ------------ ---------
MYTABLE      COL1         N
MYTABLE      COL2         N

If you disable or drop the primary key, both columns revert to the original state, co1 becomes nullable again:

如果禁用或删除主键，则两列都将恢复为原始状态，co1将再次变为可为空：

alter table mytable disable primary key;

select table_name, column_name, nullable
  from user_tab_columns where table_name = 'MYTABLE';

TABLE_NAME   COLUMN_NAME  NULLABLE
------------ ------------ ---------
MYTABLE      COL1         Y
MYTABLE      COL2         N

#3

In most DBMS, since its a primary key (and the definition of is that it must be unique within the table) then it most certainly cannot be null.

在大多数DBMS中，由于它是一个主键（并且定义在表中必须是唯一的），因此它肯定不能为空。

#4

Primary key by definition can never be Null. Primary key purpose is to uniquely identify records. A primary key is a combination of columns which uniquely specify a row.

根据定义，主键永远不能为空。主要目的是唯一地标识记录。主键是唯一指定行的列的组合。

A Null value represents lack of value. Even if two records have NULL in same column , the column values are not considered equal.

空值表示缺乏价值。即使两个记录在同一列中具有NULL，也不会将列值视为相等。

#1

create table mytable (
  col1 number primary key,
  col2 number,
  col3 number not null
);

table MYTABLE created.

select table_name, column_name, nullable 
from user_tab_cols where table_name = 'MYTABLE';

TABLE_NAME                     COLUMN_NAME                    NULLABLE
------------------------------ ------------------------------ --------
MYTABLE                        COL1                           N        
MYTABLE                        COL2                           Y        
MYTABLE                        COL3                           N