How to add primary key constraint on oracle?

oracle

Solution

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")

Problem

``` 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 ```

Original source