Do we need to specify "not null" for primary key? Oracle/SQL [closed]

Question

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?

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

Answer 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.

Answer 2

Yes, as @eaolson said, you don't need to specify NOT NULL for primary key columns, they are set automatically to 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:

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:

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:

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