CODEWITHSUNDEEP
sqloracle-database
Maryam Masood
Maryam Masood

Reputation: 17

Primary key not null

I am doing an Sql assignment in which I need to create a few tables. The assignment requires:

Make sure that you define NOT NULL constraints for the PK of each table

I don't get this. When we are defining a key as Primary Key, why should we write NOT NULL separately with it? Doesn't defining a key as PRIMARY KEY itself mean not null and unique?

Please explain!

Edit (copied from below):

CREATE TABLE Faculty(
    FacNo char(11) not null,
    FacFirstName varchar(30) not null,
    FacLastName varchar(30) not null,
    FacCity varchar(30) not null,
    FacState char(2) not null,
    FacZipCode char(10) not null,
    FacRank char(4),
    FacHireDate date,
    FacSalary decimal(10,2), 
    FacSupervisor char(11),
    FacDept char(6),
 CONSTRAINT FacultyPK PRIMARY KEY (FacNo));

Is this correct? The FACNO column is not null plus it's also a primary key.

Upvotes: 0

Views: 2578

Answers (3)

Mathias Magnusson
Mathias Magnusson

Reputation: 197

I imagine the reason your instructor asks for this is to make sure you write DDL that shows your intent clearly. Trusting auto conversion of null to not null does not help readability of DDL so I'd request all DDL to be written such that the create table statement shows the intended nullability of all columns.

Upvotes: 1

Maryam Masood
Maryam Masood

Reputation: 17

Problem solved, with the following query

CREATE TABLE Faculty(
FacNo           char(11) not null,
FacFirstName    varchar(30) not null,
FacLastName     varchar(30) not null,
FacCity         varchar(30) not null,
FacState        char(2) not null,
FacZipCode      char(10) not null,
FacRank         char(4),
FacHireDate     date,
FacSalary       decimal(10,2),
FacSupervisor   char(11),
FacDept         char(6),
CONSTRAINT FacultyPK PRIMARY KEY (FacNo) );

Upvotes: 0

are
are

Reputation: 2615

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

Upvotes: 1

Related Questions