CODEWITHSUNDEEP
oracle-database
Srabanti Das
Srabanti Das

Reputation: 51

How to add primary key constraint on oracle?

CREATE TABLE buses(Bus_no NUMBER(11) NOT NULL ,Bus_name Varchar2(20),Type VARCHAR2(20),Total_seats Number(11),Avail_seats Number(11));
ALTER TABLE buses
ADD CONSTRAINT PK_BUSES UNIQUE(Bus_no);

This is the table I need to create .. and this should be the output:

Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 BUS_NO                    NOT NULL NUMBER(11)
 BUS_NAME                       VARCHAR2(20)
 TYPE                           VARCHAR2(20)
 TOTAL_SEATS                        NUMBER(11)
 AVAIL_SEATS                        NUMBER(11)

CONSTRAINT_NAME
------------------------------
PK_BUSES

However I am getting this extra line SYS-C00403053 along with my output table,please help to remove this.

CONSTRAINT_NAME
------------------------------
SYS_C00403053
PK_BUSES

Upvotes: 5

Views: 45207

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

You haven't added a primary key, you've added a unique constraint. While a unique constraint and a not-null constraint are effectively the same, they are not actually the same as an actual primary key.

As @GurwinderSingh said, the SYS_C00403053 is a system-generated name for the not-null constraint. It is possible, but unusual, to name a not-null constraint:

-- just to clean up what you have in the question, remove the unique constraint
ALTER TABLE buses DROP CONSTRAINT PK_BUSES;

ALTER TABLE buses MODIFY Bus_no NULL;

ALTER TABLE buses MODIFY Bus_no CONSTRAINT BUS_NO_NOT_NULL NOT NULL;

desc buses

Name        Null?    Type         
----------- -------- ------------ 
BUS_NO      NOT NULL NUMBER(11)   
BUS_NAME             VARCHAR2(20) 
TYPE                 VARCHAR2(20) 
TOTAL_SEATS          NUMBER(11)   
AVAIL_SEATS          NUMBER(11)   

select constraint_name, constraint_type, search_condition
from user_constraints where table_name = 'BUSES';

CONSTRAINT_NAME                C SEARCH_CONDITION                                                                
------------------------------ - --------------------------------------------------------------------------------
BUS_NO_NOT_NULL                C "BUS_NO" IS NOT NULL

But as you want a primary key anyway, you can drop the separate not-null check, as it's implied by a (proper) primary key:

ALTER TABLE buses MODIFY Bus_no NULL;

ALTER TABLE buses ADD CONSTRAINT PK_BUSES PRIMARY KEY (Bus_no);

desc buses

Name        Null?    Type         
----------- -------- ------------ 
BUS_NO      NOT NULL NUMBER(11)   
BUS_NAME             VARCHAR2(20) 
TYPE                 VARCHAR2(20) 
TOTAL_SEATS          NUMBER(11)   
AVAIL_SEATS          NUMBER(11)   

select constraint_name, constraint_type, search_condition
from user_constraints where table_name = 'BUSES';

CONSTRAINT_NAME                C SEARCH_CONDITION                                                                
------------------------------ - --------------------------------------------------------------------------------
PK_BUSES                       P

You now only see the primary key constraint listed, but the column is still marked as not nullable, and you get the same error if you try to insert null:

insert into buses (bus_no) values (null);

ORA-01400: cannot insert NULL into ("MY_SCHEMA"."BUSES"."BUS_NO")

Upvotes: 15

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

SYS_C00403053 is the system generated name given to the NOT NULL constraint on Bus_no column. Your result is as expected only.

Upvotes: 1

Related Questions