Do We Need to Specify "Not Null" for Primary Key? Oracle/Sql

Do we need to specify not null for primary key? Oracle/SQL

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.

Primary key not null

http://www.techonthenet.com/oracle/primary_keys.php

In Oracle, a primary key is a single field or combination of fields
that uniquely defines a record. None of the fields that are part of
the primary key can contain a null value. A table can have only one
primary key.

when you set PK for a table the column will be set to NOT NULL even if you specify it as nullable

-- Create table
create table t_test_pk(
  col1 varchar2(5) null
);

SQL> desc  t_test_pk
Name Type        Nullable Default Comments 
---- ----------- -------- ------- -------- 
COL1 VARCHAR2(5) Y

so... the column is nullable

then we set PK for the table:

SQL> alter table t_test_pk add constraint pk_1 primary key (COL1);

Table altered

and try to insert null into

SQL> insert into   t_test_pk values (null);

insert into   t_test_pk values (null)

ORA-01400: cannot insert NULL into ("T_TEST_PK"."COL1")

something was changed! check in SqlPlus - the column is not nullable - and get error... we cannot insert null into the column because it was used in PK

SQL> desc t_test_pk;
Name Type        Nullable Default Comments 
---- ----------- -------- ------- -------- 
COL1 VARCHAR2(5)

OK... try to set it to nullable

SQL> alter table t_test_pk modify col1 null;

alter table t_test_pk modify col1 null

ORA-01451: column to be modified to NULL cannot be modified to NULL

What's wrong with nullable columns in composite primary keys?

Primary keys are for uniquely identifying rows. This is done by comparing all parts of a key to the input.

Per definition, NULL cannot be part of a successful comparison. Even a comparison to itself (NULL = NULL) will fail. This means a key containing NULL would not work.

Additonally, NULL is allowed in a foreign key, to mark an optional relationship.^(*) Allowing it in the PK as well would break this.

^(*)A word of caution: Having nullable foreign keys is not clean relational database design.

If there are two entities A and B where A can optionally be related to B, the clean solution is to create a resolution table (let's say AB). That table would link A with B: If there is a relationship then it would contain a record, if there isn't then it would not.

Should Foreign Key Columns be Unique and Not Null?

All databases allow foreign keys to be NULLable and non-UNIQUE. How you choose to declare a particular foreign key depends on the business case.

Consider the following tables used by a company that sells supplies to secret agents.

CountryList (
  CountryCode NOT NULL PRIMARY KEY, 
  CountryName NOT NULL
)
SecretAgents (
  CodeName NOT NULL PRIMARY KEY, 
  HomeCountryCode FOREIGN KEY REFERENCES CountryList(CountryCode)
)

Clearly, HomeCountryCode will not be unique because you may sell to more than one spy in each country. Is it NULLable? That depends on whether your business model requires each customer to declare their home country or not. If the model allows you to do business with someone who does not have a home country, or does not wish to reveal the home country to you, then the field should be NULLable. But if a state-less actor is not contemplated in your business model you should declare the column NOT NULL so that an invalid customer record cannot be created.

Now, consider the additional table

SpyMasters (
  CountryCode NOT NULL PRIMARY KEY References CountryList(CountryCode),
  Name NOT NULL PRIMARY KEY
)

This table lists the (singleton) head of spying for those countries that have a spy master. Not all countries will appear in this list, but each country can appear only once. In this case the CountryCode field is UNIQUE -- but you don't have to declare that explicitly because PRIMARY KEY always includes uniqueness.

How work PK in oracle

Disable means what you think it means.

Regarding NOT NULL and PRIMARY KEY, they are two constraints. A primary key implies NOT NULL, but if you drop the primary key constraint, the check constraint is still there. See here for details.

Regarding "I can insert null values...": this is because both constraints are disabled.

Regarding "...but not the same id twice": a primary key constraint and the unique index that is used to enforce it are two different things. Can you check if there is an unique index on this column?

Oracle - which preferable, primary key or constraint primary key

No difference at all - except that in your 1st example Oracle has to name the constraint (and it'll be something like SYS_C008630 so you have no idea what it represents), while you named the constraint as empNum_PK which shows it is the primary key.

There's yet another option: alter table:

SQL> create table employee(
  2    empNum   number(6),
  3    empName  varchar2(50));

Table created.

SQL> alter table employee add constraint empNum_PK primary key (empNum, empName);

Table altered.

SQL>

Anyway, there's still no difference as it is the primary key.

There would be a difference if it were a foreign key in cases where there's a circular reference between two tables (where each of them references another). Without alter table option, you couldn't create foreign key constraint.

This, obviously, won't work (as referenced tables don't exist yet):

SQL> create table tab_2 (id_b   number primary key,
  2                      id_a   number constraint fk_21 references table_1 (id_a));
                    id_a   number constraint fk_21 references table_1 (id_a))
                                                              *
ERROR at line 2:
ORA-00942: table or view does not exist

But, if you first create tables and then foreign keys, then it works:

SQL> create table tab_1 (id_a    number  primary key,
  2                      id_b    number);

Table created.

SQL> create table tab_2 (id_b   number primary key,
  2                      id_a   number);

Table created.

SQL> alter table tab_1 add constraint fk_12 foreign key (id_b) references tab_2 (id_b);

Table altered.

SQL> alter table tab_2 add constraint fk_21 foreign key (id_a) references tab_1 (id_a);

Table altered.

SQL>

At the end, the final result is just the same: all those keys (primary, as in your example, or foreign, as in my example) are created and functional.

What is the best practice? If you're preparing an installation script you'll run in some schema and it'll create tables, views, constraints, ..., then consider my 2nd example: create table first, and create constraints separately. Other than that, I don't think that it matters whether you create constraints within the CREATE TABLE statement or using ALTER TABLE.

SQL/Oracle - Cannot insert null into primary key

Oh I found it, the NOT NULL constraint still works fine, but my Column is actually called 'Schema' (other language) and apparently this has a definition in Oracle. So it's never altered as a primary key. Changing the table his name should work.

Thanks for the fast responds and help!

DONT USE RESERVED TERMS FOR YOUR TABLE NAMES!

Why I can not insert a row with the primary key defined as NULL?

It will work fine if you change it as you quoted above GENERATED BY DEFAULT ON NULL AS IDENTITY, do not forget to include ON NULL:

CREATE TABLE  "WS_ADDRESS"  
   ( 
    "WS_ADDRESS_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE,  
    "ADDRESS_TYPE" VARCHAR2(132),  
    "NAME" VARCHAR2(132),  
    "ADDRESS_LINE_1" VARCHAR2(132),  
    "ADDRESS_LINE_2" VARCHAR2(132),  
    "ADDRESS_LINE_3" VARCHAR2(132),  
    "TOWN" VARCHAR2(132),  
    "COUNTY" VARCHAR2(132),  
    "ZIP" VARCHAR2(132),  
    "COUNTRY" VARCHAR2(132),  
    "WS_CUSTOMER_ID" NUMBER,  
     CONSTRAINT "WS_ADDRESS_PK" PRIMARY KEY ("WS_ADDRESS_ID") 
  USING INDEX  ENABLE 
   );
insert into ws_address (ws_address_id, address_type, name) values (NULL, 'FOO', 'BAR')

Example: https://livesql.oracle.com/apex/livesql/s/l4ieh6rswg8ixmw0bscv3q9mv

Do We Need to Specify "Not Null" for Primary Key? Oracle/Sql