Do we need to specify "not null" for primary key? Oracle/SQL [closed]
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,
...
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.
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